Блог

Главная - Вопросы по программированию - T-SQL Select from JSON возвращает неверно отформатированный текст в формате JSON

T-SQL Select from JSON возвращает неверно отформатированный текст в формате JSON

#json #tsql

Вопрос:

Использование хранимой процедуры в SQL Server 2016 (включено ниже) Мне нужно сделать выбор из вложенного вывода JSON из удаленного файла, но на начальном SELECT * этапе у меня уже возникли проблемы, так как сервер возвращает, что текст JSON неправильно отформатирован:

Текст в формате JSON неправильно отформатирован. Неожиданный символ «A» находится в позиции 0.

Скрипт T-SQL, который я использую:

 CREATE PROCEDURE wget 
    @url VARCHAR(255), 
    @payload NVARCHAR(max) OUTPUT
AS
BEGIN
    DECLARE @op TABLE (op nvarchar(max))
    DECLARE @c varchar(1000) = N'powershell.exe -noprofile -executionpolicy bypass'
      N'-command (Invoke-WebRequest -Uri "' @url '" -UseBasicParsing).content'

    INSERT INTO @op 
        EXEC xp_cmdshell @c

    SELECT @payload = STRING_AGG(op, '') 
    FROM @op
END


DECLARE @jsn nvarchar(max)
EXEC wget @url = 'https://<URL-WITH-JSON-DATA>',
          @payload = @jsn output

SELECT * 
FROM OPENJSON (@jsn, N'

2 примеры массива файла JSON:


 [
    {
      "Localnumber":"2931",
      "Name":"Some Name",
      "Description":null,
      "Email":"mail@domain.dk",
      "PhoneNumbers":[
         {
            "Number":"98765432",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         },
         {
            "Number":"23456789",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         },
         {
            "Number":"34567890",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         }
      ],
      "Phones":[
         {
            "LocalNumber":"200000",
            "Name":null,
            "Type":21,
            "MAC":null,
            "BelongsTo":"2931",
            "description":"Myfone"
         },
         {
            "LocalNumber":"200048",
            "Name":null,
            "Type":8,
            "MAC":"20470396",
            "BelongsTo":"2931",
            "description":"Mobil (23456789)"
         },
         {
            "LocalNumber":"200084",
            "Name":null,
            "Type":23,
            "MAC":null,
            "BelongsTo":"2931",
            "description":"Microsoft Teams"
         },
         {
            "LocalNumber":"200125",
            "Name":null,
            "Type":8,
            "MAC":"23214202",
            "BelongsTo":"2931",
            "description":"Mobil (23456789)"
         }
      ],
      "Info":[
         {
            "Type":0,
            "Label":null,
            "Value":null
         }
      ],
      "Department":null
   },
   {
      "Localnumber":"2931",
      "Name":"Some Name",
      "Description":null,
      "Email":"mail@domain.dk",
      "PhoneNumbers":[
         {
            "Number":"98765432",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         },
         {
            "Number":"23456789",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         },
         {
            "Number":"34567890",
            "LineName":null,
            "BelongsTo":"2931",
            "GotoLocalNumber":null
         }
      ],
      "Phones":[
         {
            "LocalNumber":"200000",
            "Name":null,
            "Type":21,
            "MAC":null,
            "BelongsTo":"2931",
            "description":"Myfone"
         },
         {
            "LocalNumber":"200048",
            "Name":null,
            "Type":8,
            "MAC":"20470396",
            "BelongsTo":"2931",
            "description":"Mobil (23456789)"
         },
         {
            "LocalNumber":"200084",
            "Name":null,
            "Type":23,
            "MAC":null,
            "BelongsTo":"2931",
            "description":"Microsoft Teams"
         },
         {
            "LocalNumber":"200125",
            "Name":null,
            "Type":8,
            "MAC":"23214202",
            "BelongsTo":"2931",
            "description":"Mobil (23456789)"
         }
      ],
      "Info":[
         {
            "Type":0,
            "Label":null,
            "Value":null
         }
      ],
      "Department":null
   }
]
 


Может ли кто-нибудь указать мне правильное направление?

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

1. SQL Server не может использовать недопустимый JSON, если источник неверен, вам необходимо исправить процесс, создающий этот источник.

2. Привет @Larnu .. JSON действителен (проверен) и доставлен из крупной компании, где им ежедневно пользуются тысячи клиентов, поэтому я сомневаюсь, что JSON является основным источником сбоя, тем более что я делаю выбор неправильно.

3. Тогда SQL Server не скажет вам, что это неверно... Возможно, проблема в том, что вы делаете для использования данных на SQL Server. xp_cmdshell например, он ограничен не - MAX длиной, поэтому, если JSON содержит более 4000 символов, он будет усечен. Если вы хотите подключиться к веб-сервису с SQL Server, вам следует использовать объекты CLR.

4. Выполнили ли вы какую-либо базовую отладку своей хранимой процедуры? По крайней мере, выбрал исходные данные из @op ?

5. @Larnu Я не знал об ограничении xp_cmdshell , и я думаю, что в этом проблема, содержание данных слишком велико .. Я думаю, что сосредоточусь на изучении объектов CLR, чтобы решить эту проблему, спасибо .. не стесняйтесь публиковать ответ, если хотите.

Ответ №1:

Решение состояло в том, чтобы импортировать JSON в таблицу с переменным значением(MAX) со стороны сервера JScript:

 Response.CacheControl = "no-cache"
Response.Expires = -1
Response.CodePage = 65001
Response.CharSet = "UTF-8"

var objSrvHTTP;
objSrvHTTP = Server.CreateObject ("Msxml2.ServerXMLHTTP.6.0");
objSrvHTTP.open ("GET","<%URL_TO_JSON_DATA%>", false);
objSrvHTTP.send ();
Response.ContentType = "application/json";

А затем запросите сохраненный JSON из таблицы, используя следующий запрос:

 DECLARE @json NVARCHAR(MAX)
SET @json = (SELECT PHONE_DATA FROM EFP_JSON WHERE ID = 1)

SELECT PhoneNumber
FROM EFP_PhoneNumberSeries
WHERE REPLACE(PhoneNumber, ' ', '') NOT IN (
    SELECT PhoneNumbers.Number
    FROM OPENJSON(@json)
    WITH (   
        Localnumber VARCHAR(50) '$.Localnumber',
        Name VARCHAR(50) '$.Name',
        Email VARCHAR(50) '$.Email',
        PhoneNumbers nvarchar(max) '$.PhoneNumbers' AS JSON,
        Phones nvarchar(max) '$.Phones' AS JSON       
        ) as UserInfo
    CROSS APPLY OPENJSON(PhoneNumbers)
    WITH(
        Number nvarchar(100) '$.Number',
        LineName nvarchar(100) '$.LineName',
        GotoLocalNumber nvarchar(100) '$.GotoLocalNumber',
        BelongsTo nvarchar(100) '$.BelongsTo'
        )  as PhoneNumbers
    CROSS APPLY OPENJSON(Phones)
    WITH(
        description nvarchar(100) '$.description'
        ) as description
    ) 
AND REPLACE(PhoneNumber, ' ', '') NOT IN ('12345678','98765432')
AND (REPLACE(PhoneNumber, ' ', '') LIKE '1234%' OR REPLACE(PhoneNumber, ' ', '') LIKE '98765%');

)
2 примеры массива файла JSON:



Может ли кто-нибудь указать мне правильное направление?


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




1.  SQL Server не может использовать недопустимый JSON, если источник неверен, вам необходимо исправить процесс, создающий этот источник.  


2.  Привет @Larnu ..   JSON действителен (проверен) и доставлен из крупной компании, где им ежедневно пользуются тысячи клиентов, поэтому я сомневаюсь, что JSON является основным источником сбоя, тем более что я делаю выбор неправильно.  


3.  Тогда SQL Server не скажет вам, что это неверно…   Возможно, проблема в том, что вы делаете для использования данных на SQL Server.    xp_cmdshell    например, он ограничен не —   MAX    длиной, поэтому, если JSON содержит более 4000 символов, он будет усечен.   Если вы хотите подключиться к веб-сервису с SQL Server, вам следует использовать объекты CLR.  


4.  Выполнили ли вы какую-либо     базовую отладку    своей хранимой процедуры?   По крайней мере, выбрал исходные данные из    @op    ?  


5.  @Larnu Я не знал об ограничении   xp_cmdshell    , и я думаю, что в этом проблема, содержание данных слишком велико ..   Я думаю, что сосредоточусь на изучении объектов CLR, чтобы решить эту проблему, спасибо .. не стесняйтесь публиковать ответ, если хотите.  



Ответ №1:




Решение состояло в том, чтобы импортировать JSON в таблицу с переменным значением(MAX) со стороны сервера JScript:




А затем запросите сохраненный JSON из таблицы, используя следующий запрос:












	Метки: