Блог

Главная - Вопросы по программированию - Невозможно проанализировать Json обратно в таблицу в SQL Server

Невозможно проанализировать Json обратно в таблицу в SQL Server

#json #sql-server #parsing

#json #sql-server #синтаксический анализ

Вопрос:

Я сгенерировал документ Json, используя FOR JSON PATH в SQL Server, однако, когда я пытаюсь разобрать его обратно в таблицу, я просто получаю NULL .

 SELECT * 
FROM OPENJSON('{"Customer":[{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11060,"UnitPrice":45.6000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11061,"UnitPrice":18.0000,"ProductsOnOrder":21,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11062,"UnitPrice":12.0000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11187,"UnitPrice":43.9000,"ProductsOnOrder":20,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11217,"UnitPrice":10.0000,"ProductsOnOrder":6,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11218,"UnitPrice":18.0000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11554,"UnitPrice":55.0000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11555,"UnitPrice":13.0000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11846,"UnitPrice":25.0000,"ProductsOnOrder":16,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11847,"UnitPrice":45.6000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11992,"UnitPrice":13.2500,"ProductsOnOrder":40,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11993,"UnitPrice":21.5000,"ProductsOnOrder":20,"ShipCity":"Berlin"}]}')
WITH
(
    CustomerID NVARCHAR(255) '$.Customer'
)

Комментарии:

1. Как вы хотите, чтобы результат выглядел? Customer это свойство верхнего уровня и определенно не соответствует CustomerID . Если вы удалите WITH предложение, вы увидите, что строка проанализирована. Customer значение является массивом, хотя

2. Мне нужно преобразовать его в плоскую структуру. Имена столбцов должны быть, CustomerID, CustomerName и т.д. Вот код, сгенерировавший документ, который я пытаюсь преобразовать обратно в таблицу: ВЫБЕРИТЕ C.CustomerID, C.CustomerNumber, C.Customer, C.City, O.OrderID, O.UnitPrice, O.ProductsOnOrder, O.ShipCity ОТ клиентов C ВНУТРЕННИЕ заказы СОЕДИНЕНИЯ O НА C.CustomerID = O.CustomerID, ГДЕ CustomerNumber КАК ‘ALFKI’ ДЛЯ ПУТИ JSON, ROOT(‘Customer’)

3. Вам нужно указать выражение пути JSON, в OPENJSON котором выбираются нужные данные. WITH применяется к результату выражения пути JSON. Без этого анализируется корневой объект и возвращается одна строка для каждого атрибута. В данном случае это Customer массив значений. Если вы используете $.Customer in OPENJSON , хотя, WITH будет применен к документам внутри массива, на который указывает $.Customer

Ответ №1:

Вам нужно указать выражение пути JSON, в OPENJSON котором выбираются нужные данные. WITH применяется к результату выражения пути JSON.

Без этого анализируется корневой объект и возвращается одна строка для каждого атрибута. В данном случае это Customer массив значений. Если вы использовали OPENJSON без пути, вы получите :

 key       value              type
Customer  [{"Customer".....  4

Если бы корневой объект содержал другое свойство, например "moo":123 , оно было бы возвращено в другой строке :

 key       value              type
Customer  [{"Customer".....  4
moo       123                2

Чтобы выбрать содержимое Customer свойства, вам нужно использовать $.Customer путь. Это вернет элементы массива :

 SELECT *
FROM OPENJSON(@json,'$.Customer')

Results
-------

0   {"CustomerID":10021,"CustomerN  5
1   {"CustomerID":10021,"CustomerN  5

С now применяется к элементам массива и :

 SELECT *
FROM OPENJSON(@json,'$.Customer')
WITH(
    CustomerID NVARCHAR(255) '$.CustomerID',
    CustomerNumber NVARCHAR(255) '$.CustomerNumber',
    Customer NVARCHAR(255) '$.Customer'
)

Вернет

 CustomerID  CustomerNumber  Customer
10021       ALFKI           Alfreds Futterkiste
10021       ALFKI           Alfreds Futterkiste
10021       ALFKI           Alfreds Futterkiste

Ответ №2:

Вот так:

 declare @json nvarchar(max) = N'{"Customer":[{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11060,"UnitPrice":45.6000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11061,"UnitPrice":18.0000,"ProductsOnOrder":21,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11062,"UnitPrice":12.0000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11187,"UnitPrice":43.9000,"ProductsOnOrder":20,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11217,"UnitPrice":10.0000,"ProductsOnOrder":6,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11218,"UnitPrice":18.0000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11554,"UnitPrice":55.0000,"ProductsOnOrder":15,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11555,"UnitPrice":13.0000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11846,"UnitPrice":25.0000,"ProductsOnOrder":16,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11847,"UnitPrice":45.6000,"ProductsOnOrder":2,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11992,"UnitPrice":13.2500,"ProductsOnOrder":40,"ShipCity":"Berlin"},{"CustomerID":10021,"CustomerNumber":"ALFKI","Customer":"Alfreds Futterkiste","City":"Berlin","OrderID":11993,"UnitPrice":21.5000,"ProductsOnOrder":20,"ShipCity":"Berlin"}]}'


SELECT * 
FROM OPENJSON(@json,'$.Customer') 
      WITH
      (
        CustomerID NVARCHAR(255) '$.CustomerID'
      )
Метки: