Ngày nay dữ liệu dạng Json xuất hiện ở rất nhiều nơi trong quá trình phát triển phần mền, cũng như xây dựng các hệ thống Report, kho dữ liệu data warehouse .... và được lưu trữ ở các hệ quản trị cơ sở dữ liệu NoSQL như Mongo DB, elasticsearch ... Vì vậy SQL Server cung cấp nhiều function để xử lý dữ liệu dạng Json như :

• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)
• OPENJSON (Transact-SQL).

  ISJSON() : Chức năng kiểm tra data có đúng cấu trúc là một json

  Để mọi người dể hình dung và có dữ liệu để thao tác thì mình đã chuẩn bị data để mọi người có thể thực hành trực tiếp trên SQL Server của mình.

-- Tạo database : DATATEST
IF DB_ID(N'DATATEST') IS NULL
CREATE DATABASE DATATEST;
GO
USE DATATEST
GO
-- Tạo table : [dbo].[Technology]
CREATE TABLE [dbo].[Technology] (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR (256),
    [Object]  NVARCHAR(max)
) 
GO
-- Tạo data với Object là  @language nhưng đặt biệt
-- nó không phải dạng Json
DECLARE @language NVARCHAR(MAX);
SET @language = '{
    "value1"  : "SQL",
    "value2"  : "JavaScript - JS",
    "value3"  ::::: "Java",
    "value4"  :    "C#",
    "value5"  :    "Ruby",
    "value6"  :    "Python"
}'
-- Tạo data với Object là @Framework là data dạng json
DECLARE @Framework NVARCHAR(MAX);
SET @Framework ='{
    "value7"  :    ".NET - C#",
    "value8"  :    "SPRING FRAMEWORK - JAVA",
    "value9"  :    "REACTJS",
    "value10" :    "VUE JS"
}'
-- Insert data vào table [dbo].[Technology]
INSERT INTO [dbo].[Technology] (Id, name, [Object]) 
VALUES 
(NEWID(),'Language', @language),
(NEWID(),'Framework',@Framework)
GO

Sau khi run xong script trên mọi người sẻ có được database DATATEST trong SQL Server và bạn vào query table [dbo].[Technology] sẻ được kết quả.

SELECT [Id]
      ,[name]
      ,[Object]
FROM [DATATEST].[dbo].[Technology]

Để kiểm tra column Object của 2 row trên là data dạng Json không bằng cách sử dụng ISJSON() function trong trong mệnh đề WHERE của câu query, điều khiện JSJSON([Object]) > 0.

SELECT [Id]
      ,[name]
      ,[Object]
FROM [DATATEST].[dbo].[Technology]
WHERE ISJSON([Object]) > 0

Kết quả chỉ có đúng 1 row với column [name] = Framework và data trong column [Object] là data dang Json.

Thực tế khi làm việc với dữ liệu dạng Json trong SQL Server để dễ nhìn cấu trúc dạng json mình dùng trang https://jsonformatter.curiousconcept.com/# này để xem data như thế lào. :)

JSON_VALUE (@JSON, Path) chức năng của function này lấy "data hay value" của thuộc tính trong 1 Object Json dựa vào "name hay key hay đường dẫn (Path) đến key" của thuộc tính.

Nếu dữ liệu là 1 Object Json thì sẻ được đặt trong dấu {} và cặp dữ liệu "name thuộc tính" : "giá trị của thuộc tính" hay gọi là (Key : value).

Bây giờ chúng ta quay lại với table [dbo].[Technology], nhiệm vụ của mình là tách column Object dạng Json này thành các column dựa vào key của data json dựa vào JSON_VALUE () function.

USE [DATATEST];
GO
SELECT [Id]
      ,[name]
      ,JSON_VALUE([Object], '$.value7') AS value7
      ,JSON_VALUE([Object], '$.value8') AS value8
      ,JSON_VALUE([Object], '$.value9') AS value9
      ,JSON_VALUE([Object], '$.value10') AS value10
FROM [DATATEST].[dbo].[Technology]
WHERE ISJSON([Object]) >0

Kết quả : Trong thực tế mọi người sẻ gặp dạng data json rất phức tạp, tùy vào yêu cầu chúng ta sẻ trích xuất data dựa vào node của chuỗi Json.

Ví dụ chuổi json dưới, mình đặt tên nó là [ info]. Yêu cầu lấy ra data "country" trong chuổi json [ info]. Chúng ta phân tích "country" nằm trong object "address", tiếp đến "address" lại nằm trong object "info" => Path = '$.info.address.country'

{
   "info": {
      "type": 1,
      "address": {
         "town": "Bristol",
         "county": "Avon",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}

DECLARE @info NVARCHAR(MAX);
SET @info = '{
   "info": {
      "type": 1,
      "address": {
         "town": "Bristol",
         "county": "Avon",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}'

SELECT JSON_VALUE(@info, '$.info.address.country') AS [country]

Kết quả :

JSON_QUERY (@JSON, Path) chức năng của function này lấy ra bất kì Object Json hoặc array Json trong chuổi Json dựa vào path name.

Để chi tiết thì mình sẻ sử dụng lại data info trên. Nhiệm vụ chúng ta sẻ lấy ra Object "address" trong chuổi Json info. Vậy path = $.info.address

DECLARE @info NVARCHAR(MAX);
SET @info = '{
   "info": {
      "type": 1,
      "address": {
         "town": "Bristol",
         "county": "Avon",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}'

SELECT JSON_QUERY(@info, '$.info.address') AS [address]

Kết quả :

Bạn cũng có thể lấy ra data cấu trúc array trong chuổi json. Ví dụ chúng ta có thể lấy ra array "tags" trong chuổi json "info".

DECLARE @info NVARCHAR(MAX);
SET @info = '{
   "info": {
      "type": 1,
      "address": {
         "town": "Bristol",
         "county": "Avon",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}'

SELECT JSON_QUERY(@info, '$.info.tags') AS [tags]

Kết quả :

Sự khác nhau giữa JSON_VALUE và JSON_QUERY . Bạn cứ xem JSON_QUERY là cấp trên của JSON_VALUE, JSON_QUERY sẻ lấy ra data dạng Object or array, ví dụ như lấy ra "address" or tags. Còn JSON_VALUE sẻ lấy data của 1 thuộc tính trong 1 Object, ví dụ như lấy data của thuộc tính "town" trong object "address" hoặc "type" trong Object "info".

SON_MODIFY (@Json, path, new value) function này mục đích giúp chúng ta cập nhật lại value của thuộc tính trong chuổi json.

Ví dụ nhữ bạn muốn update "country" trong chuổi Json này thành "Viet Nam".

DECLARE @info NVARCHAR(MAX);
SET @info = '{
   "info": {
      "type": 1,
      "address": {
         "town": "Bristol",
         "county": "Avon",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}'
SELECT JSON_MODIFY(@info, '$.info.address.country', 'Viet Nam')

Kết quả

OPENJSON (Transact-SQL) function này giúp chúng ta có thể trích xuất chuổi json để phục vụ cho quá trình tạo một data dạng bảng (table).

OPENJSON( @json, [path] )
WITH ( 
    [Tên cột] [Kiểu dữ liệu] [Path] ,
    ......... .............. ......
)

Ví dụ 1 : dữ liệu kiểu object thì khi sử dụng OPENJSON bạn sẻ được table bao gồm column : Key, value , type.

DECLARE @json NVARCHAR(2048) = N'{
   "Null_value": null,
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Kết quả :

Key : là thuộc tính của Object, value là giá trị tương ứng với thuộc tính đó, type sẻ được qui định , 0 là value có kiểu dự liệu NULL, 1 là value có kiểu dữ liệu String, 2 là value có kiểu dữ liệu number, 3 là value có kiểu dữ liệu boolearn, 4 là value có kiểu dữ liệu dạng mảng, 5 là value có kiểu dữ liệu dạng Object.

Ví dụ 2 : Tách dữ liệu với data là một mảng Object.

[  
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}  
]

Yêu cầu tách mỗi Object trong array Json này thành table với column : id, name, surname, age, skills.

DECLARE @json NVARCHAR(MAX);
SET @json = N'[  
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}  
]';

SELECT *  
FROM OPENJSON(@json)  
WITH (
    id INT '$.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',  
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)

Kết quả : Tài liệu :

JSON data in SQL Server: (docs.microsoft.com/en-us/sql/relational-dat..)