Доступ к массиву JSON в SQL Server 2016 с использованием JSON_VALUE

Я застрял при доступе к массиву внутри json с помощью недавно введенной функции JSON_VALUE. Пожалуйста, рассмотрите следующий код -

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData')
    DROP TABLE JsonData;
go

CREATE TABLE JsonData(JsonData nvarchar(max));
DECLARE @SQL nvarchar(max);
DECLARE @Table AS TABLE(JsonPath VARCHAR(256));

INSERT INTO JsonData(JsonData)
VALUES(
'{
  "firstName": "John",
  "lastName" : "doe",
  "age"      : 26,
  "address"  : {
    "streetAddress": "naist street",
    "city"         : "Nara",
    "postalCode"   : "630-0192"
  },
  "phoneNumbers": [
    {
      "type"  : "iPhone",
      "number": "0123-4567-8888"
    },
    {
      "type"  : "home",
      "number": "0123-4567-8910"
    }
  ]
}')


INSERT INTO @Table
SELECT VALUE  FROM OPENJSON('{
"Path1":"$.firstName","Path2":"$.phoneNumbers[:1].number"
}') ;

SELECT  @SQL=(SELECT 'UNION SELECT '''+ CAST(JsonPath AS VARCHAR(256)) +''',JSON_VALUE(JsonData,'''+a.JsonPath+''')  
                    FROM JsonData a'                             
                    FROM @Table a       
        FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)')
FROM @Table t;

SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)

PRINT @SQL    

EXEC SP_EXECUTESQL @SQL;

Здесь, если я хочу получить доступ к определенному номеру телефона, обычный синтаксис доступа к этому узлу не работает. Я получаю следующую ошибку в этом случае

JSON path is not properly formatted. Unexpected character ':' is found at position 15.

Хотя, когда я проверил http://jsonpath.com, я смог получить значение. Использует ли SQL Server 2016 другой синтаксис для доступа к значениям JSON?


json sql sql-server sql-server-2016
person UVData    schedule 09.07.2016    source источник
comment
Это не по теме, но может быть полезно знать. Вы можете использовать DROP TABLE IF EXISTS JsonData вместо if (select) drop table....   -  person Jovan MSFT    schedule 19.07.2016
comment
Согласитесь, от старых привычек трудно избавиться :-)   -  person UVData    schedule 30.07.2016

Ответы (4)


arrow_upward
22
arrow_downward

Чтобы получить все из phoneNumbers:

DECLARE @json nvarchar(max)=
    '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }'

    SELECT [Type], [Number]
    FROM OPENJSON( @json, '$.phoneNumbers' ) 
    WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');
person Igor Micev    schedule 12.07.2016
comment
Спасибо еще раз! Похоже, это самое близкое, что я могу получить. Если я не знаю точной структуры предоставленного JSON, потребуется много настроек, но ваши предложения помогли мне в правильном направлении. Я хотел создать универсальное решение. Я хочу, чтобы будущие обновления могли поддерживать правильное перемещение JSON. - person UVData; 12.07.2016

arrow_upward
14
arrow_downward

Вы можете использовать «CROSS APPLY», чтобы получить номера телефонов с firstName:

SELECT  JSON_VALUE (jsonData, '$.firstName'),p.*
    FROM JsonData
    CROSS APPLY
    OPENJSON (JsonData, '$.phoneNumbers') WITH(type varchar(10) '$.type', number varchar (30) '$.number') p
person Bochen Lin    schedule 14.08.2017

arrow_upward
5
arrow_downward

SQL Server 2016 поддерживает JSON. Он очень похож, почти идентичен. Вы сделаете свое собственное сравнение.

Вам не нужно использовать временную переменную @Table, а затем производить манипуляции...

Просто запустите следующие запросы

SELECT  JSON_VALUE( JsonData, '$.phoneNumbers[0].type' ) AS [PhoneType], 
        JSON_VALUE( JsonData, '$.phoneNumbers[0].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--iPhone 0123-4567-8888

SELECT  JSON_VALUE( JsonData, '$.phoneNumbers[1].type' ) AS [PhoneType], 
        JSON_VALUE( JsonData, '$.phoneNumbers[1].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--home  0123-4567-8910

Ознакомьтесь с этими официальными ссылками от Microsoft о поддержке JSON для получения более подробной информации:

https://msdn.microsoft.com/en-us/library/dn921897.aspx

https://msdn.microsoft.com/en-us/library/dn921898.aspx

person Igor Micev    schedule 10.07.2016
comment
Привет, спасибо за помощь! Мне нужна была переменная @table, так как моему исходному решению нужен параметр, в котором приложение может передавать любой путь JSON. Данные по этому переданному пути должны быть возвращены из (НАМНОГО) сложного JSON. Это упрощенный пример, и мне в основном нужно вернуть все элементы, если массив предоставляется в качестве аргумента. Например, $.phoneNumbers[:].number должен возвращать все номера в этом массиве. - person UVData; 12.07.2016
comment
Ахам, тогда ты очень близок к разгадке. Я бы использовал вышеуказанные запросы (или пути) в динамическом SQL-запросе, когда вы пытаетесь выполнить свой запрос. Знайте, что $.phoneNumbers[:] в T-SQL JSON на самом деле $.phoneNumbers[*] - person Igor Micev; 12.07.2016
comment
Спасибо еще раз! Я получил следующую ошибку, когда попытался использовать * - Msg 13607, уровень 16, состояние 4, путь JSON строки 4 неправильно отформатирован. Неожиданный символ '*' найден в позиции 15. Если бы вы могли указать мне любую ссылку, объясняющую TSQL JSON, это было бы действительно полезно. не нашел такой ссылки - person UVData; 12.07.2016
comment
Тогда «[*]» является неправильным синтаксисом. Я не проверял это (предполагая, что [:] работает, и ожидал, что [*] тоже будет работать. См. Мой пост ниже, чтобы узнать, как получить все строки. Тогда вам нужно использовать OPENJSON. - person Igor Micev; 12.07.2016
comment
спасибо, JSON_VALUE(c.value,'$.stock_asset_rents[0].sequence_num') как [stock_asset_rents_sequence_num], работал для доступа к массиву json - person Golden Lion; 17.11.2020

arrow_upward
0
arrow_downward

Все вместе.

DECLARE @json NVARCHAR(MAX)
    = '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }';

SELECT
    Core.*
   ,ARRAY.[Type]
   ,ARRAY.[Number]
FROM
    OPENJSON(@json)
        WITH
        (
            FirstName NVARCHAR(25) '$.firstName'
           ,LastName NVARCHAR(25) '$.lastName'
           ,Age INT '$.age'
           ,streetAddress NVARCHAR(25) '$.address.streetAddress'
           ,city NVARCHAR(25) '$.address.city'
        ) AS Core
    CROSS APPLY
    OPENJSON(@json, '$.phoneNumbers')
        WITH
        (
            [Type] NVARCHAR(25) '$.type'
           ,[Number] NVARCHAR(25) '$.number'
        ) AS ARRAY;
person Charles    schedule 26.10.2018