Azure SQL: создание JSON со столбцом в качестве ключа

У меня есть следующие данные в моей таблице сервера sql

ИмяЦенностьValueHash
странааааззз
фамилиясссгггг
Эл. адрес[email protected]ххх
имябббwww

И я хочу, чтобы приведенный ниже Json использовал sql-запрос

{
    "lastname": {
      "value": "ccc",
      "valueHash": "yyy"
    },
    "email": {
      "value": "[email protected]",
      "valueHash": "xxx"
    },
    "firstName": {
      "value": "bbb",
      "valueHash": "www"
    },
    "country": {
      "value": "aaa",
      "valueHash": "zzz"
    }
}

Я мог бы придумать следующий запрос

select Value as 'value', ValueHash as 'valueHash'  from user
where id=752594
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Что возвращает

    {
      "value": "ccc",
      "valueHash": "yyy"
    },
    {
      "value": "[email protected]",
      "valueHash": "xxx"
    },
    {
      "value": "bbb/T1B+4nzpVhb0M",
      "valueHash": "www"
    },
    {
      "value": "aaa",
      "valueHash": "zzz"
    }

Пробовал решение от сгенерировать json со значением столбца в качестве ключа json dict, но получаю ошибку компилятора.

Может кто-нибудь, пожалуйста, помогите мне с этим? ТИА

В вашей таблице есть больше, чем просто эти столбцы, если в вашем запросе есть WHERE id=752594. Я предполагаю, что id - это идентификатор, который связывает строки для формирования интересующего вас JSON?

squillman 06.05.2022 15:53
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
35
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Это дает аналогичные результаты. Указание SQL Server не использовать оболочку массива для внутренних значений приводит к экранированию результатов:

WITH YourTable AS(
    SELECT *
    FROM (VALUES('country','aaa','zzz'),
                ('lastname','ccc','yyy'),
                ('email','[email protected]','xxx'),
                ('firstName','bbb','www'))V(Name,Value,ValueHash))
SELECT (SELECT value,
               ValueHash
        WHERE YT.Name = 'lastname'
        FOR JSON PATH) AS lastname,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'email'
        FOR JSON PATH) AS email,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'firstName'
        FOR JSON PATH) AS firstName,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'country'
        FOR JSON PATH) AS country
FROM YourTable YT
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Я не могу жестко закодировать значения запроса результата, такие как 'email' lastname firstName country в самом запросе. Мне нужен общий, как в другом ответе, так как мне нужно сгенерировать json для разных пользователей, и каждый пользователь имеет разные значения Name

OTUser 06.05.2022 16:19

Это примечание, которое вы должны были включить в свой вопрос, @OTUser.

Larnu 06.05.2022 16:40
Ответ принят как подходящий

К сожалению, в SQL Server нет JSON_AGG или JSON_OBJECT_AGG. Так что вам нужно взломать его с помощью STRING_AGG и STRING_ESCAPE

SELECT
  '{' +
  STRING_AGG(
  CONCAT(
    '"',
    STRING_ESCAPE(u.Name, 'json'),
    '":',
    v.json
  ), ','
 ) + '}'
FROM [user] u
CROSS APPLY (
    SELECT
      u.Value AS value,
      u.ValueHash AS valueHash
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) v(json)
WHERE u.id = 752594;

дб <> рабочий пример

Спасибо, ваше решение работает, я заметил одну вещь: некоторые из моих хеш-значений в БД похожи на R5/T1B, но в json я получаю их как R5/T1B, есть ли способ получить исходное хеш-значение без дополнительных escape-символов в результате json

OTUser 06.05.2022 16:16

Вот как SQL Server избегает этого, он немного осторожен в побеге. Это не имеет значения, потому что, когда вы десериализуете его с помощью любого приличного десериализатора, он не будет экранирован.

Charlieface 06.05.2022 16:23

Если вы когда-либо хотели вернуть более одного объекта, предполагая, что у вас есть столбец id в таблице, как вы, кажется, подразумеваете с вашим предложением WHERE, вы можете сделать что-то вроде этого:

DECLARE @t TABLE (id INT,
                  Name VARCHAR(100),
                  Value VARCHAR(100),
                  ValueHash VARCHAR(100));
INSERT @t (id, Name, Value, ValueHash)
VALUES (1, 'country', 'aaa', 'zzz'),
       (1, 'lastname', 'ccc', 'yyy'),
       (1, 'email', '[email protected]', 'xxx'),
       (1, 'firstName', 'bbb', 'www'),
       (2, 'country', 'aaa2', 'zzz2'),
       (2, 'lastname', 'ccc2', 'yyy2'),
       (2, 'email', '[email protected]', 'xxx2'),
       (2, 'firstName', 'bbb2', 'www2');


SELECT (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'lastname'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) lastname,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'email'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) email,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'firstName'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) firstName,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'country'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) country
FROM @t t1
WHERE t1.Name = 'lastname'
FOR JSON PATH;

Обратите внимание, что, как и в случае с решением Ларну, он также будет избегать значений для Value и ValueHash.

Я не могу жестко закодировать значения запроса результата, такие как 'email' lastname firstName country в самом запросе. Мне нужен общий, как в другом ответе, так как мне нужно сгенерировать json для разных пользователей, и каждый пользователь имеет разные значения Name

OTUser 06.05.2022 16:22

Да, видел этот комментарий к ответу Ларну, когда я публиковал свой... Было бы неплохо узнать заранее.

squillman 06.05.2022 16:24

Другие вопросы по теме