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..

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Apr 4, 2022 at 19:01
I included the code attempts? The current query is my code attempts.. I will create a sql fiddle... — hollecar
– hollecar, Commented Apr 4, 2022 at 19:09
I keep trying to create a sql fiddle with this ``` CREATE TABLE TblName1 ([id] varchar(25), [orf_seq_xxx] varchar(max),[isCircular] varchar(10), [schemaId] varchar(75)) ; INSERT INTO TblName1 ([id], [orf_seq_xxx],[isCircular],[schemaId]) VALUES ( 'seq_aaaaaa', 'seq_aaaaaa,seq_aaaaaa,seq_aaaaaa,seq_aaaaaa,seq_aaaaaa', 'false', 'ts_aaaaa1' ), ( 'seq_bbbbbb', 'seq_bbbbbb,seq_bbbbbb,seq_bbbbbb,seq_bbbbbb,seq_bbbbbb', 'false', 'ts_bbbbb1' ), ( 'seq_cccccc', 'seq_cccccc,seq_cccccc,seq_cccccc,seq_cccccc,seq_cccccc', 'false', 'ts_ccccc1' ) ; ``` — hollecar
– hollecar, Commented Apr 4, 2022 at 19:31
I get this error every time Database 'db_18_e5c1bd' already exists. Choose a different database name. — hollecar
– hollecar, Commented Apr 4, 2022 at 19:32

Charlieface · Accepted Answer · 2022-04-04 20:09:02Z

2

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

edited Apr 4, 2022 at 20:09

answered Apr 4, 2022 at 19:46

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel Liew Over a year ago

Comments are not for extended discussion; this conversation has been moved to chat.

Collectives™ on Stack Overflow

SQL Server Nested object with FOR JSON PATH

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related