Constructing nested json arrays in T-SQL

Question

Given the table:

C1   C2    C3
----------------
1   'v1'  1.1
2   'v2'  2.2
3   'v3'  3.3

Is there any "easy" way to return JSON in this format:

{
  "columns": [ "C1", "C2", "C3" ],
  "rows": [
    [ 1, "v1", 1.1 ],
    [ 2, "v2", 2.2 ],
    [ 3, "v3", 3.3 ]
  ]
}

To generate an array with single values from a table there is a neat trick like this:

 SELECT JSON_QUERY(REPLACE(REPLACE(
 (
    SELECT id
    FROM table a
    WHERE pk in (1,2)
    FOR JSON PATH
 ), '{"id":',''),'}',''))   'ids'

Which generates

"ids": [1,2]

But to construct the nested array above the replacing gets really tedious, anyone know a good way to achieve this?

I think it's going to be easier to generate this structure concatenating strings than for json with replace. You might want to look here for ideas — Zohar Peled
– Zohar Peled, Commented Dec 1, 2018 at 14:19

Gottfried Lesigang · Accepted Answer · 2018-12-01 23:49:48Z

Well, you ask for an easy way but the following will not be easy :-)

The tricky part is to know which values need to be qouted and which can remain naked.

This needs generic type-analysis to find, which values are strings.

The only way I know to get on meta data (besides building dynamic sql using meta views like INFORMATIONSCHEMA.COLUMNS) is XML together with an AUTO-schema.

This XML is very near to your needs actually. There is a list of columns at the beginning, followed by a list of rows. But it is not JSON of course...

Try this out:

--This is a mockup table with the values you provided.

DECLARE @mockup TABLE(C1 INT,C2 VARCHAR(100),C3 DECIMAL(4,2));
INSERT INTO @mockup VALUES
 (1,'v1',1.1)
,(2,'v2',2.2)
,(3,'v3',3.3);

--Now we create an XML out of this

DECLARE @xml XML =
(
    SELECT *
    FROM @mockup t 
    FOR XML RAW,XMLSCHEMA,TYPE
);

--Check the XML's content with SELECT @xml to see how it is looking internally

--Now the real query can start:

SELECT '{"columns":[' + 
STUFF(@xml.query('declare namespace xsd="http://www.w3.org/2001/XMLSchema";
                  for $col in /xsd:schema/xsd:element//xsd:attribute
                  return
                  <x>,{concat("""",xs:string($col/@name),"""")}</x>
                  ').value('.','nvarchar(max)'),1,1,'') +

'],"rows":[' +
STUFF(
(
    SELECT
    ',[' + STUFF(b.query('  declare namespace xsd="http://www.w3.org/2001/XMLSchema";
                            for $attr in ./@*
                            return
                            <x>,{if(/xsd:schema/xsd:element//xsd:attribute[@name=local-name($attr)]//xsd:restriction/@base="sqltypes:varchar") then
                                    concat("""",$attr,"""")
                                    else 
                                    xs:string($attr)
                                }
                            </x>
                         ').value('.','nvarchar(max)'),1,1,'') + ']'
    FROM @xml.nodes('/*:row') B(b)
    FOR XML PATH(''),TYPE
    ).value('.','nvarchar(max)'),1,1,'') +
']}';

The result

{"columns":["C1","C2","C3"],"rows":[[3,"v3",3.30],[1,"v1",1.10],[2,"v2",2.20]]}

Some explanation:

The first part will use XQuery to find all columns (xsd:attribute within XML-schema) and create the array of column names.

The second part will againt use XQuery in order to run through all rows and write their column values in a concatenated string. Each value can refer to its type within the schema. Whenever this type is sqltypes:varchar the value will be quoted. All other values remain naked.

This will not solve each and any case generically...

To be honest, this was more for my own curiosity :-) Wanted to find out, how one can solve this.

Quite probably the best answer is: Use another tool. SQL-Server is not the best choice here ;-)

Thanks for your work, this was indeed a nice solution and I will mark it as accepted. Well I agree and think T-SQL's JSON support have alot to ask for.

Collectives™ on Stack Overflow

Constructing nested json arrays in T-SQL

1 Answer 1

Some explanation:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Some explanation:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related