אינדוקס שדה JSON

Question

בוקר טוב,

יש לי שאלה בנוגע לאינדוקס שדה JSON.

יש לי שדה שמורכב ממערך של רשימת חשבונות בנק הכולל פרטי חשבון בנק, מספר חשבון, קוד סניף קוד בנק וכו. בתוך השדה יכולים להיות פרטים של מספר חשבונות ולכן זהו כמובן מערך אובייקים של JSON

הסתכלתי כאן אולם יש כאן דוגמה לאינדוקס של שדה אולם זה לא מתאים - כי השדה שאני מנסה לאנדקס - מערך אובייקטים ולא על אובייקט יחיד.

https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver15

ניסיתי לעשות view ולעשות עליו אינדוקס אולם הוא מסרב משום שמעורב ב view הפרדיקט cross apply

יש פתרון לדבר הזה???

Answer 1

אהלן 😀

זו אחת השאלות הכי מעניינות שדנו עליהם בסביבות שנת 2016 כאשר נוספה התמיכה ב JSON

באותה תקופה, העברתי הרצאה בדיוק בנושא הזה של אינדוקס ובכלל על המיטוב בעבודה עם JSON בשרתי SQL Server  והצגתי כמה רעיונות 

זה נושא מעניין, אבל מכיוון שהתשובה לא מובנית, צריךל לקיים מעט ניותר דיון וכרגע אין לי זמן.

אני צריך להתארגן קצת לפני מפגש שאנחנו מעבירים היום בעוד שעתיים בערך - אתה מוזמן כמובן להצטרף

https://www.meetup.com/msc-il/events/281780421

אני אנסה לחזור לכאן היום בליל או מחר (מקווה לא לשכוח)

בכל מקרה צריך לחשוב מחוץ לקופסא מכיוון ש JSON בשרתי SQL זה לא סוג נתונים אלא רק תמיכה לטקסט שיש לו פורמט (תוכן) של JSON. הקיחשור שהבאתי בהחלט מציג את הבסיס למקרה הפשוט.

חשוב! בינתיים,

(1) תשקיע קצת ותעלה קוד (שאילתות) ליצירה של טבלה ולהכנסת כמה שורות לדוגמה כדי להמחיש את המקרה שאתה מדבר עליו

(2) תתאר לנו בדיוק מה התוצאה המצופה שאתה רוצה לקבל מתוך הטבלה והשורות לדוגמה.

(3) תעלה את הדוגמה לניסיונות שלך - זה יכול לתת כיוון גם להבנה וגם אולי לפתרון ברוח הדברים

נוכל לקיים דיון כאשר תהיה לנו דוגמה מוחשית :-)  

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 2

תודה על המענה המהיר.

מבנה השדה הוא נראה כך 

[
  {
    "bank_account_id": 1,
    "bank_account_number": "784512",
    "bank_code": "14",
    "bank_branch_code": "315"
  },
  {
    "bank_account_id": 2,
    "bank_account_number": "98653232",
    "bank_code": "10",
    "bank_branch_code": "605"
  },
  {
    "bank_account_id": 3,
    "bank_account_number": "98651111",
    "bank_code": "10",
    "bank_branch_code": "605"
  }
]

ה- view שמציג לי את רשימת הלקוחות, קוד בנק, קוד סניף ומספר חשבון נראה בערך כך :

SELECT te.id_entity,
       te.first_name,
       te.last_name,
       te.entity_name,
       te.entity_number,
       lg.bank_account_id,
       lg.bank_account_number,
       lg.bank_branch_code,
       lg.bank_code    
FROM dbo.tbl_entities te
CROSS APPLY (
  SELECT bank_account_id,
         bank_account_number,  
         bank_branch_code,     
         bank_code
  FROM OPENJSON(te.bank_accounts)
  WITH (
  bank_account_id INT '$.bank_account_id'
  , bank_account_number NVARCHAR(100) '$.bank_account_number' 
  , bank_branch_code NVARCHAR(100) '$.bank_branch_code'  
  , bank_code NVARCHAR(100) '$.bank_code'  
  )
) AS lg
WHERE te.bank_accounts IS NOT NULL

בטבלה יש כמה מאות אלפי רשומות (בשלב זה) והמטרה שלי זה לאנדקס את השדות הללו לטופת חיפוש מהיר.

 

Answer 3

אהלן,

(1) ביקשתי שתעלה DDL+DML ז"א קוד ליצירת הטבלה ולהכנסת כמה שורות לדוגמה, כדי שנוכל לעבוד עלהם.

אני לא יכול להריץ את יצירת ה View אם אין לי את הטבלה המתאימה, ובשלב הבא להריץ שאילתות על הטבלה כדי למטב את העבודה איתה אם אין לנו כמה שורות לעבוד איתן.

בינתיים אני כן יכול לתת כמה כיוונים כללליים שיכולים לעזור, אבל תספק בבקשה את המידע החסר כדי שנוכל לשחזר את המצב בקלות

(2) אל תתמקד בארכיטקטורה הקיימת, בניסיון לשלב אליה משהו אשר ייתן את הפתרון. ייתכן מאוד שהפתרון המיטבי יהיה בסוף לבטל לחלוטין את השימוש ב view למשל ולבנות את הטבלה מעט שונה ולהריץ שאילות ישירות על הטבלה ה"ממוטבת".

למשל בקישור שהבאת מופיע רעיון מוכר, של יצירת טור מחושב Commuted column ואז יוצרים אינדקס על הטור המחושב. ז"א אנחנו משנים את מבנה הטבלה כדי למטב את העבודה עם המידע בטבלה. למעשה אנחנו מוסיפים מידע כפול, אשר כבר נמצא בטור של ה JSON ומחלצים אותו לטור נוסף על מנת לאנדקס רק לפי החלק הזה. הגישה הזו מנוגדת לרעיון הבסיסי של מסד נתונים רלציוני והחוקים של הנירמול, והולך לכיוון מחשבה ותכנון של מסד נתונים data warehouse - בו אנחנו שומרים ועובדים עם מידע אחרי ETL (כאשר גודל מסד הנתונים אינו פרמטר חשוב כמו הצורך לעבוד עם המידע המעובד ולכן שומרים מידע כפול אחרי עיבוד כלשהו).

(3) מה המטרה שלך ב View? 

בשורה התחתונה, מה אתה צריך? ה view הוא אמצעי בדרך כלל. אם השאילתה הסופית שלך היא בסגנון select * from view אז לשם מה היית צריך את ה view ולא הרצת שאילת ישירה?. תנסה להבהיר את המחשבה מאחורי הלקעים ואת המטרה הסופית כדי שנוכל לבנות את הארכיטקטורה המתאימה לך

הערה בהקשר לסעיף 3: הרבה פעמים כאשר באים לפורום שואלים שאלה על X בעוד השאלה הנכונה היא בכלל על המקור של X שהוא Y ולא X. לבניות כאלה קוראים בעיות XY ויש הסבר טוב מאוד בויקיפדיה למה הכוונה. לכן אנחנו צריכים להבין מה המטרה האמיתי ולא מה האמצעי שמובנה כבר על "חצי פתרון" מפני שאולי נוותר על החצי לחלוטין ונציע פתרון שונה.

https://en.wikipedia.org/wiki/XY_problem

> והמטרה שלי זה לאנדקס את השדות הללו לטופת חיפוש מהיר

תציג לנו דוגמה של השאילתות חיפוש שאתה רוצה להריץ. חיפוש לפי איזה שדות למשל

זו הנקודה הכי חשובה בכל השאלה ואין לנו מידע עליה עדיין :-) 

האם יש לי כבר כיוון פתרון?!?

לא, מכיוון שחסר מידע, אבל במבט ראשון בהחלט ייתכן שהפתרון שיתאים מבוסס למשל על טריגר אשר יעבד את המידע בזמן הכנסה או עידכון הטור של ה JSON ויבנה את המידע בצורה כזו שתתאים לגישה מהירה.

אנא ספק את המידע החסר כדי שיהיה לנו עם מה לעבוד

 את כל שלושה הנקודות שביקשתי שתספק לנו כבר מההתחלה

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 4

תודה על התגובה

מצורף כאן הסריפט ליצירת טבלה

CREATE TABLE dbo.tbl_entities
  (
    id_entity     UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWSEQUENTIALID()),
    first_name    NVARCHAR(255)    NULL,
    last_name     NVARCHAR(255)    NOT NULL,
    entity_number NVARCHAR(255)    NOT NULL,
    entity_name   AS (CONCAT_WS(' ', [first_name], [last_name])),
    bank_accounts NVARCHAR(MAX)    NULL,
    CONSTRAINT PK_tbl_entities1_id_entity PRIMARY KEY CLUSTERED (id_entity)
  ) ON [PRIMARY]
    TEXTIMAGE_ON [PRIMARY]
GO

וכאן סקריפט להוסיף נתונים 

INSERT INTO tbl_entities (first_name, last_name, entity_number, bank_accounts)
VALUES ('Alex', 'Aharonov', '00001', N'[
  {
    "bank_branch_code": 653,
    "bank_code": 10,
    "bank_account_number": 456123,
    "bank_account_id": 1
  },
  {
    "bank_branch_code": 633,
    "bank_code": 10,
    "bank_account_number": 456124,
    "bank_account_id": 2
  }
]'),
('Eli', 'Shimon', '00002', N'[
  {
    "bank_branch_code": 477,
    "bank_code": 12,
    "bank_account_number": 987654,
    "bank_account_id": 1
  },
  {
    "bank_branch_code": 859,
    "bank_code": 10,
    "bank_account_number": 456120,
    "bank_account_id": 2
  }
]'),
('Tami', 'Shani', '00003', N'[
  {
    "bank_branch_code": 477,
    "bank_code": 4,
    "bank_account_number": 98754,
    "bank_account_id": 1
  },
  {
    "bank_branch_code": 859,
    "bank_code": 20,
    "bank_account_number": 321654,
    "bank_account_id": 2
  },
  {
    "bank_branch_code": 455,
    "bank_code": 11,
    "bank_account_number": 741852,
    "bank_account_id": 3
  },
  {
    "bank_branch_code": 711,
    "bank_code": 15,
    "bank_account_number": 369258,
    "bank_account_id": 4
  }
]')

דוגמה חשובה שבה אני אני צריך לתת מענה - איתור לקוח לפי בנק, חשבון וסניף

select * from vw_bank_accounts where 
bank_account_number = @bank_account_number and 
bank_branch_code = @bank_branch_code
and bank_code    = @bank_code    

Answer 5

הקפצה :)

Answer 6

אהלן

> שדה JSON

אין בשרתי SQL כרגע סוד JSON כך שאין ולא יכול להיות גם אינדקס מיוחד כמו שיש למשל בסוג XML

התמיכה ב JSON היא תמיכה בעבודה עם טקסט בפורמט JSON ולא תמיכה בשמירה של JSON

ז"א טסט של JSON אנחנו שומרים בטור טקסטואלי פשוט כמו למשל NVARCHAR  או VARCHAR כאשר כדאי להקפיד לעבוד עם UTF-8 אם בחרת להשתמש ב VARCHAR

מה שיש לנו זה פונקציות שמעבדות את הטקסט בפורמט JSON

אז מה הפתרון לאינדוס?

יש מספר פתרונות כאשר הנפוץ ביותר הוא שימוש בטור מחושב - ז"א לנתח בצורה דינאמית בעזרת הפונקציות של JSON את המידע הטקסטואלי של ה JSON ואת המידע המחושב שומרים בטור מחושב אשר אפשר לאנדקס

> השדה שאני מנסה לאנדקס - מערך אובייקטים ולא על אובייקט יחיד.

כאמור הטור (אין שדות דרך אגב בטבלאות אלא רק טורים ושורות) שכולל את הטקסט של ה JSON הוא לא JSON אלא טקסט ואין בו מערכים או אביייקטים. אם לא הצלחת למצוא ייצוג מתאים (כמו למשל במקרה של מערך) שתוכל לשים בטור מחושב אז אין לך אפשרות לאנדקס

במקרה שלך אתה יכול למשל להחזיק טבלה מקושרת ששם ישמר המידע של המערכים - זו הצורה שבה SQL Server נותן פתרון ליחסים של יחיד לרבים (יחיד: הערך הטקסטואלי של ה JSON המקורי או השורהף רבים: הטבלה המקושרת עם מפתח ראשי ומשני אשר כוללת את כל הערכים של המערך) 

את הדטא של הטבלה המקורת אפשר לנהל למשל עם טריגרים על הטבלה המקורית

צריך לזכור ששרתי SQL הם עדיין שרתים יחסיים טבלאיים ולא שרתים של NoSQL. ניהול המידע של משמכי JSON נעשה כטקסט רגיל ונופל עלינו - לא לצפות לקבל את אותן ביצועים כמו בעבודה עם מסד נתונים שמובנה על מסמכי JSON כמובן

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]