content top
SQL 2016 ו JSON הלכה למעשה חלק ב

SQL 2016 ו JSON הלכה למעשה חלק ב

את המאמר מלווה script בשם SQL_2016_JSON_DEMO.sql עם הדוגמאות המלאות .

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

החל מ SQL 2005 השתמשנו ב AdventureWorks ו AdventureWorksDW כבסיסי נתונים לדוגמא עבור OLTP ו OLAO . מ SQL 2016 יש sample databases חדשים שנקראים WideWorldImporters ו WideWorldImportersDW . כול הדוגמאות של Microsoft הן ב github עבור SQL Server והן עבור SQL Azure . אנחנו נשתמש בבסיסי הנתונים WideWorldImporters ו WideWorldImportersDW שהם עבור סביבות OLTP ו OLAP בהתאמה עבור גרסאות Enterprise או Evaluation של SQL Server . הגיבויים בהם השתמשתי הם בכתובות WideWorldImporters-Full.bak ו WideWorldImportersDW-Full.bak בהתאמה . מעתה ואיך אני אשתמש בדוגמאות מבסיסי הנתונים הללו .

עלינו לזכור שכרגע אין Data type מסוג JSON. אני מניח שבגרסאות הבאות יהיה כזה דבר או לחילופין תמיכה של SQL Server במנוע נוסף כמו ה Document DB . JSON מאוחסן כ varchar או nvarchar והוא מאנדקס כטקסט רגיל לכול דבר .

בשביל לראות את הפלט בפורמט של JSON אפשר להשתמש ב JSON Viewer .לשם כך ניתן להתקין Notepad ++ ותוסף בשם JSONViewer Notepad++ plugin.יש להוריד את ההתקנה מהכתובת

https://sourceforge.net/projects/nppjsonviewer/files/latest/download

JSON Path Expression

בשביל לפנות ל property של אובייקט JSON נשתמש ב JSON Path Expression . הסינטקס דומה לזה של Java script . השימוש ב JSON Path Expression הוא בפונקציות הבאות ב SQL 2016 : OPENJSON , JSON_VALUE , JSON_QUERY ו JSON_MODIFY. אני אדגים את השימוש הזה בפונקציה OPENJSON.

OPENJSON

כדי להפוך JSON לפורמט טבלאי של שורות ועמודות נשתמש בפונקציה OPENJSON שהיא table-value function (TVF) . OPENJSON זמין רק עבור compatibility level של 130 ומעלה. אם ה compatibility level נמוך מ 130 הפונקציה OPENJSON לא מאופשרת . פונקציות JSON האחרות זמינות בכול ה compatibility level . בתור תרגיל שנו compatibility level של DB ל 100 , 110 ו 120 . תריצו שאילתה עם FOR JSON AUTO על אחת הטבלאות ותראו שמתקבלת תוצאה בפורמט של JSON ללא הודעת שגיאה .

יש 2 דרכים להשתמש ב OPENJSON : implicit schema ו explicit schema .עבור האופציה implicit schema נשתמש ב default schema. במקרה זה הפונקציה מחזירה שורה עבור כל property של האובייקט או עבור כל אלמנט של מערך . הנה הדוגמא הראשונה עבור implicit schema בה נגדיר משתנה מסוג nvarchar(max) , נציב בתוכו אובייקט מסוג JSON , ונקרא לפונקציה OPENJSON עם משתנה זה . הפלט בצורה טבלאית הוא Property של כל אובייקט (העמודה key) , הערך (העמודה value) ועמודה מסוג type עם ערך 1 עבור מחרוזת ו 2 עבור שדה int .


USE [WideWorldImporters];

-- Ex 1 : JSON with implicit schema : Return each property of an object

DECLARE @JSON NVARCHAR(MAX);

SET @JSON = '{

    "StockItemName": "Ride on toy sedan car ",

    "StockItemId": 68,

    "SearchDetail": "Ride on toy sedan car (Red) 1/12 scale Suits child to 20

    kg",

    "UnitPrice": 230,

    "Brand": "Northwind"

    }'

SELECT * FROM OPENJSON(@JSON)

פלט :

image

בדוגמא הבאה עבור implicit schema נגדיר מערך מסוג JSON עם 3 איברים . מערך ב JSON מסומן בסוגריים מרובעות . הפלט דומה לדוגמא הקודמת והפעם נקבל בפלט את כל האלמנטים של המערך.


-- EX 2 : JSON with implicit schema : Return each element of an array

DECLARE @JSON1 NVARCHAR(MAX);

SET @JSON1 = '[

    "USB missile launcher ",

    "Complete with 12 projectiles ",

    230

    ]'

SELECT * FROM OPENJSON(@JSON1)

פלט :

image

ב explicit schema יש לקרוא לפונקציה OPENJSON עם שם האובייקט והפרמטר השני הוא נתיב בצורה של JSON Path Expression . הפלט יוגדר בצורה מפורשת בביטוי with .הסימן $ מסמן את האובייקט . אחרי $ יכול להיות רשימה של key names או אלמנט במערך שבניהם יש נקודה . בדוגמא שלנו : $.StockItemIs.StockItemsDetail הוא הנתיב אליו אנו רוצים להתייחס בביטוי with , והוא רשימה של key names . בביטוי with הגדרנו את העמודות הנדרשות בפלט , ה data type ואת הנתיב היחסי עבור כל עמודה מהמקור $.StockItemIs.StockItemsDetail . אפשר לראות בשאילתה ש category ו Brand הם בהיררכיה ישירות מתחת ל StockItemsDetail . לעומת זאת ל StockItemName ו UnitPrice יש אבא אחר בשם StockItemsDetail , ולכן כאשר ציינו אותם ציינו גם את StockItemDetails . שימו לב שאם ה property לא מוגדר יוחזר ערך NULL . בדוגמא MarketingComments לא מוגדר ב StockItemDetails עבור המוצר השני , ולכן נקבל בפלט NULL תחת השדה comments בשורה השנייה .


-- Ex 4: OPENJSON with an explicit schema

DECLARE @JSON4 NVARCHAR(MAX);

SET @JSON4 = N'{"StockItemIs": 

    {"StockItemsDetail":

    [

    {

        "Category": "USBs",

        "Brand": "Samsung",

        "StockItemDetails": {

            "StockItemName": "USB rocket launcher (Gray)",

            "MarketingComments": "Suits child to 20 kg",

            "UnitPrice": 25

            }

    },

    {

    "Category": "Toys",

    "Brand": "FisherPrice",

    "StockItemDetails": {

        "StockItemName": "Ride on toy sedan ",

        "UnitPrice": 223.0

        }

}

]

}

}'

SELECT * FROM

OPENJSON ( @JSON4, '$.StockItemIs.StockItemsDetail' )

WITH (

    Category VARCHAR(200) '$.Category' ,

    Subcategory VARCHAR(200) '$.Brand' ,

    Name VARCHAR(200) '$.StockItemDetails.StockItemName',

    Price DECIMAL(18,2)'$.StockItemDetails.UnitPrice',

    Comment VARCHAR(200) '$.StockItemDetails.MarketingComments'

)

פלט :

image

ב JSON Path Expression ניתן להגדיר בהתחלה 2 mode אפשריים :

lax – ב mode זה , שהוא בררת המחדל , הפונקציה מחזירה ערך ריק אם JSON Path Expression . למשל אם ביקשת את $.StockItemIs והוא לא קיים , הפונקציה תחזיר null . בדוגמא הקודמת עבור ה key MarketingComments לא מוגדר ב StockItemDetails עבור המוצר השני , ולכן נקבל בפלט NULL תחת השדה comments בשורה השנייה .

ב strict mode , אם יש שגיאה הפונקציה תחזיר שגיאה .

בדוגמא הבאה נהפוך את JSON לטבלה זמנית . הפעם שוב נשתמש ב implicit schema כלומר לא נשתמש באופציה with של OPENJSON . אחרי ההסבר לדוגמא הקודמת , השאילתה והפלט די ברורים. בפלט בעמודה Type קיבלנו type מסוג 4 וסוג 5 שמשמעותם array ו object בהתאמה .


-- EX 3: Convert JSON to a temporary table with implicit schema

DECLARE @JSON3 NVARCHAR(MAX);

SET @JSON3 = N'{

    "StockItemsDetail": {

    "Brand": "Samsung",

    "DetailedInfo": {

        "Name": "USB missile launcher",

        "Description": "USB missile launcher with ties",

        "UnitPrice": 15

    },

    "Components":

        [

        "Tiles",

        "Screw",

        "Cooper"

        ]

    }

}'

SELECT * FROM OPENJSON(@JSON3, N'lax $.StockItemsDetail')

SELECT * FROM OPENJSON(@JSON3, N'lax $.StockItemsDetail.DetailedInfo')

פלט :

image

פונקציות JSON של SQL 2016

ל SQL 2016 יש את הפונקציות הבאות שתומכות ב JSON :

· ISJSON – בודקת שטקסט הוא בפורמט JSON תקין .

· JSON_VALUE – מוציאה ערך של סקלר ממחרוזת JSON .

· JSON_QUERY – מוציאה ערך של מערך או אובייקט ממחרוזת JSON .

· JSON_MODIFY – מעדכנת ערך של property או מחרוזת JSON ומחזירה את המחרוזת המעודכנת.

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

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


-- Usung ISJSON functionto Validate JSON text 

DECLARE @tmp TABLE

(

[Id] INT,

[JSON_COL] NVARCHAR(MAX)

)

INSERT INTO @tmp

VALUES

(1, '{

    "StockItemName": "USB rocket launcher (Gray)",

    "MarketingComments": "Suits child to 20 kg",

    "UnitPrice": 25,

    "Date": "2016-08-02T00:00:00"

    }'),

(2, '{

    "StockItemName": "Ride on toy sedan car ",

    "MarketingComments": "Complete with 12 tiles,

    "UnitPrice": 223,

    "Date": "2015-08-04T00:00:00"

    }')

SELECT * FROM @tmp

WHERE ISJSON([JSON_COL]) > 0

GO

פלט :

image

כשאר נתקן את המחרוזת השנייה , ונוסיף מירכאות לאחר המחרוזת tiles השאילתה תחזיר את 2 השורות . הנה הפלט אחרי התיקון , השאילתה המתוקנת ב script SQL_2016_JSON_DEMO.sql:

הפונקציה ISJSON מצוינת בכדי לבדוק שהפורמט המידע הוא בפורמט JSON לפני שמבצעים החלפת מידע עם ה Client .

להתראות במאמרים הבאים !


5 תגובות על “SQL 2016 ו JSON הלכה למעשה חלק ב”

  1. מאת Amy:

    Ahaa, its good discussion on the topic of this article
    at this place at this weblog, I have read all that, so at this time me also commenting at this place. http://www.yahoo.net

  2. Con el deseo que hayan tenido una breve presentación de nuestra empresa, les invitamos a que si desean alguna información más
    específica, se pongan en contacto con nosotros sin ningún género de compromiso. http://www.iranldp.org/?option=com_k2&view=itemlist&task=user&id=1095150

  3. מאת jumbo:

    Thanks for the information.

  4. Hello there! This is my first visit to your blog!
    We are a group of volunteers and starting a new initiative in a
    community in the same niche. Your blog provided us useful information to work on. You have done a outstanding job!

  5. מאת eryaman escort:

    Hi to every single one, it's in fact a nice for me to visit this web page, it includes helpful Information.

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

nineteen − 9 =