Set a column value as key in JSON output

Question

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)?

Answer 1

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);