{
"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..
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 withSTRING_AGG
(to aggregate),STRING_ESCAPE
(to escape quotes) andJSON_QUERY
(to prevent double-escaping).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: