Question 212856 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Set a column value as key in JSON output

Suppose I have this SQL working:

SELECT(
    SELECT 
        a.id,
        a.label,
        a.text
    FROM [data] a 
    FOR JSON PATH, INCLUDE_NULL_VALUES
) AS 'data'

which will produce JSON with this structure:

[
    {
        "id": "N/A",
        "label": "test",
        "text": "Not applicable"
    },
    {
        "id": "ID",
        "label": "test2",
        "text": "Identification"
    }
]

What I would like to have is this structure – the value of the label column used as key.

[{
    "test": {
        "id": "N/A",
        "text": "Not applicable"
    },
    "test2": {
        "id": "ID",
        "text": "Identification"
    }
}]

Is it possible, with SQL (not building the JSON by strings)?

Tags:

2

Answers


  1. 0

    You could use FOR XML to concatenate/aggregate the FOR JSON PATH of id and text

    SELECT N'[{'+STUFF((
SELECT N',"'+STRING_ESCAPE(D.label,'json')+N'":',(SELECT D.id,D.text FOR JSON PATH,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER)
FROM data D FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),1,1,N'')+N'}]'
    Login or Signup to reply.
  2. 0

    Unfortunately, SQL Server does not yet support JSON_OBJECT_AGG, which would have worked well for this.

    You can instead use STRING_AGG and STRING_ESCAPE, with a nested FOR JSON subquery.

    This works for SQL Server 2017+.

    SELECT
  data = '[{' +
    STRING_AGG(
      '"' + STRING_ESCAPE(a.label, 'json') + '":' + j.json,
      ','
    ) + '}]'
FROM [data] a
CROSS APPLY (
    SELECT
      a.id,
      a.text
    FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
) j(json);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search