I have the below data in my sql server table

And I want the below Json using sql query

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

I could come up with the below query

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

Which returns

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

Tried the solution from generate json with column value as json dict key but am getting compiler error.

Can someone please help me with this? TIA

CodePudding user response：

Unfortunately, SQL Server does not have JSON_AGG or JSON_OBJECT_AGG. So you need to hack it with STRING_AGG and 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;

db<>fiddle

CodePudding user response：

This produces a similar results. Telling SQL Server to not use an array wrapper on the inner values results in it escaping the results:

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;

CodePudding user response：

If you ever wanted to return more than a single object, assuming you have a column id in the table as you seem to imply with your WHERE clause, you can do something like this:

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;

Note, as with Larnu's solution it will also escape the values for Value and ValueHash.