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, но получаю ошибку компилятора.
Может кто-нибудь, пожалуйста, помогите мне с этим? ТИА
Ответы 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.
К сожалению, в 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
Вот как SQL Server избегает этого, он немного осторожен в побеге. Это не имеет значения, потому что, когда вы десериализуете его с помощью любого приличного десериализатора, он не будет экранирован.
Если вы когда-либо хотели вернуть более одного объекта, предполагая, что у вас есть столбец 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
Да, видел этот комментарий к ответу Ларну, когда я публиковал свой... Было бы неплохо узнать заранее.
В вашей таблице есть больше, чем просто эти столбцы, если в вашем запросе есть
WHERE id=752594. Я предполагаю, чтоid- это идентификатор, который связывает строки для формирования интересующего вас JSON?