SQL Server 2016: интеграция JSON

Идея этой статьи состоит в том, чтобы перечислить как можно больше примеров вложенных элементов JSON, включение примеров данных, преобразование реляционных данных в данные JSON, JSON в реляционные данные, преобразование элементов JSON в отдельные столбцы и представление тех же данных в отдельные строки.

JSON означает JavaScript Object Notation. JSON является основным представлением данных для всех баз данных NoSQL . Это естественно подходит для разработчиков, которые используют JSON в качестве формата обмена данными в своих приложениях. Относительная способность JSON (записи JSON хорошо структурированы, но легко расширяются) в отношении его масштабируемости привлекла разработчиков, ищущих миграции БД в гибких средах. Объем данных и схемы могут быть трудно изменить. Перезапись большого набора данных, хранящегося на диске, при одновременном сохранении связанных приложений в сети может занять много времени. Для обновления данных могут потребоваться дни фоновой обработки (в умеренных и больших примерах).

Фон

Большинство традиционных механизмов реляционных баз данных теперь поддерживают JSON. С SQL Server 2016 легко обмениваться данными JSON между приложениями и ядром базы данных. Microsoft предоставила различные функции и возможности для анализа данных JSON. Они пытались перенести данные JSON в реляционное хранилище. Он также обеспечивает возможность преобразования реляционных данных в данные JSON и JSON в денормализованные данные.

Наличие этих дополнительных функций JSON, встроенных в SQL Server, должно упростить для приложений обмен данными JSON с SQL Server. Эта функциональность обеспечивает гибкость интеграции данных JSON в ядро ​​реляционной базы данных. Разработчики могут писать и изобретать сложные запросы на своих периодических этапах процесса разработки.

Реляционные базы данных относятся к традиционному хранилищу данных, конструктивному и интуитивно понятному языку SQL, разработке сложных запросов и соответствию ACID. NoSQL предлагает разные концепции — сложные структуры помещаются вместе в коллекции сущностей, где вы можете взять все, что вам нужно, с помощью одной операции чтения, или где вы можете вставить сложные структуры с одной записью, следуя теореме CAP.

Реляционные базы данных в некоторой степени нормализуют данные; то есть вместо того, чтобы повторять фрагмент данных в нескольких строках, в таблице, которая нуждается в этой информации, будет храниться внешний ключ, который указывает на другую таблицу, в которой хранятся данные. С другой стороны, этот процесс означает, что данные обычно удаляются из своей первоначальной формы для размещения в таблицах, а затем повторно собираются во время выполнения, объединяя таблицы в ответ на запрос. Это становится особенно дорогим по мере роста набора данных, и данные должны быть распределены между несколькими серверами баз данных.

Синтаксис JSON получен из синтаксиса нотации объектов JavaScript.

• Данные находятся в парах имя / значение. {«Ключ» «значение»} — наиболее распространенный формат для объектов.
• Данные разделяются запятыми. { «Клавиша»»значение»}, { „клавиша“»значение»}.
• Фигурные скобки удерживают объекты. { «Ключ» { «ключ»»значение»}}.
• Квадратные скобки содержат массивы. {«Ключ» [{«ключ» «значение»}, {«ключ» «значение»}]}.

Ценности JSON

В JSON значения должны быть одним из типов данных, указанных ниже.

• Строка
• Число
• Объект (объект JSON)
• Массив
• Логическое значение
• Значение NULL

Если у вас есть отношения родитель / потомок (Fact / Dimension), где связанная дочерняя информация не часто меняется, и вам нужно читать дочерние записи вместе с родителем без дополнительных JOINS, вы можете сохранить дочерние записи в родительской таблице как JSON массив. В  традиционных базах данных процесс нормализации обеспечивает дублирование минимизированного объема информации, тогда как в NoSQL он намеренно дублирует ее, чтобы упростить ее использование. Допустим, мы хотим представить количество студентов, посещающих занятия. Нормализованный способ представления данных приведен ниже. Использование массива обозначает данные измерений реляционной таблицы:

{ 
    course "Basic programming", 
        room "1A", 
        students[{ 
            id 1, 
            name "Prashanth"
        }, { 
            id 2, 
            name "Jayaram"
        }] 
}

[{ 
    course "Basic programming", 
    room "1A", 
    studentId 1, 
    studentName "Prashanth"
}, { 
    course "Basic programming", 
    room "1A", 
    studentId 2, 
    studentName "Jayaram"
}]

CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2))

INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10)
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10)
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30)
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)

(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')

SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON AUTO

SELECT E.EMPNO,E.ENAME,D.DEPTNO  AS "DEPARTMENT.DEPTNO" ,D.DNAME AS "DEPARTMENT.DNAME",D.LOC AS "DEPARTMENT.LOCATION"
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON PATH

SELECT E.EMPNO,E.ENAME,D.DEPTNO  AS "DEPARTMENT.DEPTNO" ,D.DNAME AS "DEPARTMENT.DNAME",D.LOC AS "DEPARTMENT.LOCATION"
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON PATH,ROOT('EMPLOYEE')

[{ 
    "id": "0001", 
    "type": "donut", 
    "name": "Cake", 
    "ppu": 0.55, 
    "batters": { 
        "batter": [{ 
            "id1": "1001", 
            "type1": "Regular"
        }, { 
            "id1": "1002", 
            "type1": "Chocolate"
        }, { 
            "id1": "1003", 
            "type1": "Blueberry"
        }, { 
            "id1": "1004", 
            "type1": "Devils Food"
        }] 
    }, 
    "topping": [{ 
        "id2": "5001", 
        "type2": "None"
    }, { 
        "id2": "5002", 
        "type2": "Glazed"
    }, { 
        "id2": "5005", 
        "type2": "Sugar"
    }, { 
        "id2": "5007", 
        "type2": "Powdered Sugar"
    }, { 
        "id2": "5006", 
        "type2": "Chocolate with Sprinkles"
    }, { 
        "id2": "5003", 
        "type2": "Chocolate"
    }, { 
        "id2": "5004", 
        "type2": "Maple"
    }] 
}, { 
    "id": "0002", 
    "type": "donut", 
    "name": "cup Cake", 
    "ppu": 0.5, 
    "batters": { 
        "batter": [{ 
            "id1": "1001", 
            "type1": "Regular"
        }, { 
            "id1": "1002", 
            "type1": "Chocolate"
        }, { 
            "id1": "1003", 
            "type1": "Blueberry"
        }, { 
            "id1": "1004", 
            "type1": "Devil's Food"
        }] 
    }, 
    "topping": [{ 
        "id2": "5001", 
        "type2": "None"
    }, { 
        "id2": "5002", 
        "type2": "Glazed"
    }, { 
        "id2": "5005", 
        "type2": "Sugar"
    }] 
}]

SELECT ID, type, name, ppu, type1 batter, type2 topping FROM
OPENROWSET(BULK N '\\hq6021\c$\stocks.json', SINGLE_CLOB) AS json 
CROSS APPLY OPENJSON(BulkColumn) 
WITH(id nvarchar(40), type nvarchar(40), name NVARCHAR(MAX), ppu NVARCHAR(MAX), batters NVARCHAR(MAX) AS JSON, topping NVARCHAR(MAX) AS JSON) AS t 
CROSS APPLY 
OPENJSON(batters, '$.batter') 
WITH(id1 nvarchar(100), type1 nvarchar(20)) 
CROSS APPLY 
OPENJSON(topping) 
WITH(id2 nvarchar(100), type2 nvarchar(20))

DECLARE @MyJSON NVARCHAR(4000) = N '{  
"info" { 
    "type" 
    1, "address" { 
        "town" 
        "Louisville", "county" 
        "Boulder", "country" 
        "USA" 
    }, "tags" ["Snow", "Breweries"] 
}, "LocationType" 
"East", "WeatherType" 
"Cold" 
} 
'
Select * from OPENJSON(@MyJSON) 
WITH(type int '$.info.type', LocationType varchar(20) 
    '$.LocationType', WeatherType varchar(20) 
    '$.WeatherType', town varchar(200) 
    '$.info.address.town', county varchar(200) 
    '$.info.address.county', country varchar(200) 
    '$.info.address.country') AS t 
CROSS APPLY 
OPENJSON(@MyJSON, '$.info.tags')

DECLARE @json NVARCHAR(1000) 
SELECT @json = N '{  
"Employee" [{ 
    "Element"
    1
}, { 
    "Element"
    2
}, { 
    "Element"
    "n"
}] 
} 
'

DECLARE @MyJSON NVARCHAR(4000) = N '{   
"EmployeeDepartment": [{ 
    "EmployeeID": "E0001", 
    "FirstName": "Prashanth", 
    "LastName": "Jayaram", 
    "DOB": "1983-02-03", 
    "DEPT": [{ 
        "EmployeeDepartment": "Ducks" 
    }, { 
        "EmployeeDepartment": "Blues" 
    }] 
}, { 
    "EmployeeID": "E0002", 
    "FirstName": "Prarthana", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "EmployeeDepartment": "Red Wings" 
    }] 
}, { 
    "EmployeeID": "E0003", 

    "FirstName": "Pravitha", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "EmployeeDepartment": "Red Wings" 
    }, { 
        "EmployeeDepartment": "Green Bird" 
    }] 
}] 
} 
'
--SELECT * FROM OPENJSON(@MyJSON) 
SELECT
EmployeeID, 
FirstName, 
LastName, 
DOB, 
DEPT, 
EmployeeDepartment 
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment') 
WITH(EmployeeID varchar(10), FirstName varchar(25), LastName varchar(25), DOB varchar(25), DEPT NVARCHAR(MAX) AS JSON) AS E 
CROSS APPLY 
OPENJSON(DEPT) 
WITH(EmployeeDepartment nvarchar(100))

DECLARE @MyJSON NVARCHAR(4000) = N '{   
"EmployeeDepartment": [{ 
    "EmployeeID": "E0001", 
    "FirstName": "Prashanth", 
    "LastName": "Jayaram", 
    "DOB": "1983-02-03", 
    "DEPT": [{ 
        "DeptID": "D1", 
        "DName": "Ducks" 
    }, { 
        "DeptID": "D2", 
        "DName": "Blues" 
    }] 
}, { 
    "EmployeeID": "E0002", 
    "FirstName": "Prarthana", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "DeptID": "D3", 
        "DName": "Red Wings" 
    }] 
}, { 
    "EmployeeID": "E0003", 
    "FirstName": "Pravitha", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "DeptID": "D3", 
        "DName": "Red Wings" 
    }, { 
        "DeptID": "D4", 
        "DName": "Green Bird" 
    }] 
}] 
} 
'
SELECT
JSON_Value(c.value, '$.EmployeeID') as EmployeeID, 
    JSON_Value(c.value, '$.FirstName') as FirstName, 
    JSON_Value(C.value, '$.DOB') as DOB, 
    JSON_Value(p.value, '$.DeptID') as DEPTID, 
    JSON_Value(p.value, '$.DName') as DName 
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment') as c 
CROSS APPLY OPENJSON(c.value, '$.DEPT') as p

DECLARE @MyJSON NVARCHAR(4000) = N '{   
"EmployeeDepartment": [{ 
    "EmployeeID": "E0001", 
    "FirstName": "Prashanth", 
    "LastName": "Jayaram", 
    "DOB": "1983-02-03", 
    "DEPT": [{ 
        "DeptID": "D1", 
        "DName": "Ducks" 
    }, { 
        "DeptID": "D2", 
        "DName": "Blues" 
    }] 
}, { 
    "EmployeeID": "E0002", 
    "FirstName": "Prarthana", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "DeptID": "D3", 
        "DName": "Red Wings" 
    }] 
}, { 
    "EmployeeID": "E0003", 
    "FirstName": "Pravitha", 
    "LastName": "Prashanth", 
    "DOB": "2015-07-06", 
    "DEPT": [{ 
        "DeptID": "D3", 
        "DName": "Red Wings" 
    }, { 
        "DeptID": "D4", 
        "DName": "Green Bird" 
    }] 
}] 
} 
'
SELECT
EmployeeID, 
FirstName, 
DOB, 
Dept1, DName1, 
Dept2, DName2 
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment') 
WITH(EmployeeID varchar(20) 
    '$.EmployeeID', FirstName varchar(20) 
    '$.FirstName', DOB varchar(20) 
    '$.DOB', Dept1 varchar(20) 
    '$.DEPT[0].DeptID', Dname1 varchar(20) 
    '$.DEPT[0].DName', Dept2 varchar(20) 
    '$.DEPT[1].DeptID', Dname2 varchar(20) 
    '$.DEPT[1].DName') AS EMP