Блог

Главная - Вопросы по программированию - Ошибка при использовании CONVERT при выборе из OPENJSON

Ошибка при использовании CONVERT при выборе из OPENJSON

#sql #json #tsql #sql-server-2017

#sql #json #tsql #sql-server-2017

Вопрос:

Учитывая переменную @PartsData , которая является a VARCHAR(MAX) и содержит набор данных json, этот запрос работает нормально:

 SELECT 
       *
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
        ) A

Но, предположим, я хочу преобразовать некоторые данные, когда я открываю их из jason:

 SELECT 
       *
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
            PhraseHash UNIQUEIDENTIFIER CONVERT([uniqueidentifier],hashbytes('MD2',concat('$.PartDescription', '$.CultureName'))
        ) A

При этом я получаю:

Неправильный синтаксис рядом с ключевым словом ‘CONVERT’.

Это работает нормально, но мне нужно вычисленное значение в оригинале WITH , чтобы я мог присоединиться к нему:

 SELECT 
       *
       , CONVERT([uniqueidentifier],hashbytes('MD2',concat(CAST(A.PartDescription as NVARCHAR(max)),A.CultureName))) AS PhraseHash
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
        ) A

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

1. Разве вы не можете взять последний запрос, который «работает нормально», и поместить его во вложенный выбор, а Join затем обратно OPENJSON в преобразованный столбец. Или используйте a CTE и Join это обратно OPENJSON .

2. Да, и это будет работать. Но я надеялся избежать этого и найти ярлык 🙂

3. @CaseyCrookston CONVERT там нет места. OPENJSON предназначен для анализа строк JSON, а не для применения функций. Это задача ВЫБОРА, ПЕРЕКРЕСТНОГО ПРИМЕНЕНИЯ и т. Д.

Ответ №1:

Предложение WITH — неподходящее место для этого, но вы можете использовать a CROSS APPLY для вычисления необходимого значения по строке и использовать это значение в соединении. Что-то в этом примере:

 DECLARE  @tbl TABLE(ID INT,SomeValue VARCHAR(100));
INSERT INTO @tbl VALUES
 (1020,'Values 10 and 20')
,(2030,'Values 20 and 30');

DECLARE @json NVARCHAR(MAX)=
N'{
    "rows":
     [{"id":"1","val1":"10","val2":"20"},
      {"id":"2","val1":"20","val2":"30"},
      {"id":"3","val1":"20","val2":"30"},
      {"id":"4","val1":"40","val2":"30"}
    ]
  }';

  SELECT *
  FROM OPENJSON(@json,'$.rows')
  WITH(id INT,val1 NVARCHAR(10),val2 NVARCHAR(10)) A
  CROSS APPLY(SELECT CAST(CONCAT(A.val1,A.val2) AS INT)) B(ConcatenatedAndCasted)
  INNER JOIN @tbl t ON t.ID=B.ConcatenatedAndCasted;

Для вашего следующего вопроса: пожалуйста, попробуйте настроить MCVE (как я сделал выше) самостоятельно. Добавьте примерные данные и ожидаемый результат. Добавьте свои собственные попытки и объясните, почему ваш вывод не соответствует вашим потребностям. Это поможет гораздо больше, чем тысяча слов…

Обновить

Вы также можете получить производную таблицу из своих данных JSON, как здесь:

   WITH JSON_Data AS
  (
  SELECT *
  FROM OPENJSON(@json,'$.rows')
  WITH(id INT,val1 NVARCHAR(10),val2 NVARCHAR(10)) A
  )
  SELECT * FROM  @tbl t
  INNER JOIN JSON_Data jd ON t.ID=CAST(CONCAT(jd.val1,jd.val2) AS INT);

На самом деле существуют различные подходы с использованием CTE, вложенных запросов, applies или даже временных таблиц. Какой из них лучше для вас, зависит от количества строк и ваших потребностей…