SQL Server Nested object with FOR JSON PATH

Question

{
    "dnaSequences": [
        {
            "id": "seq_fdfdfd",
            "fields": {
                "ORF": [
                    "seq_aaaaaa",
                    "seq_bbbbbbbb",
                    "seq_ccccccccc",
                    "seq_ddddddddd"
                ]
            },
            "isCircular": false,
            "schemaId": "ts_fdfdf"
        }
    ]
}

I'm trying to create this JSON above with the FOR JSON PATH in SQL Server...

This is the query so far...but i cant seem to get the double quotes correct around the nested objects in the ORF array? Also the values in the ORF are comping from one field in multiple records.

SELECT top 1 id,
       (SELECT top 3 orf_seq_xxx AS 'fields.ORF'
        FROM vw_viewName
        FOR JSON PATH) AS ORF,
       [isCircular],
       [schemaId]
FROM vw_viewNameFOR JSON PATH, ROOT('dnaSequences');

field: orf_seq_xxx is created in a sql view by concatenating data together..

SUBSTRING((SELECT top 5 ',' + 'seq_aaaaa_' AS 'data()'FROM [v_viewName] FOR XML PATH('')), 2 , 9999)As orf_seq_xxx

you can ignore the top 5 and the top 3 in the sql...I only have this to limit the amount of data..

Answer 1

You need to nest fields.ORF in a subquery.

Unfortunately SQL Server does not support JSON_AGG, which would have made things simpler. Instead we need to hack it with STRING_AGG (to aggregate), STRING_ESCAPE (to escape quotes) and JSON_QUERY (to prevent double-escaping).

SELECT
  t.id,
  [fields.ORF] = JSON_QUERY((
      SELECT '[' + STRING_AGG('"' + STRING_ESCAPE(s.value, 'json') + '"', ',') + ']'
      FROM STRING_SPLIT(t.orf_seq_xxx, ',') s
  )),
  isCircular = CASE WHEN t.isCircular = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END,
  t.schemaId
FROM TblName1 t
FOR JSON PATH, ROOT('dnaSequences');

SQL Fiddle

Doing this using the base tables, rather than having to split and re-aggregate, would be easier and certainly more performant. If you were querying the base table you would probably have something like this:

SELECT
  t.id,
  [fields.ORF] = JSON_QUERY(
      '[' + STRING_AGG('"' + STRING_ESCAPE(t.orf_seq_xxx, 'json') + '"', ',') + ']'
  ),
  isCircular = CASE WHEN t.isCircular = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END,
  t.schemaId
FROM BaseTable t
GROUP BY
  t.id,
  t.isCircular,
  t.schemaId
FOR JSON PATH, ROOT('dnaSequences');