How to return a nested JSON array

Question

I have a table with 3 columns as shown on Pic1.

Pic1:

My goal is to return a nested array as a JSON string:

"[
   [7157688981.272619,7290098.188727271,null,null,null,null,null], 
   [9331221970.409422,-187354647.1071058,2949162.807512622,null,null,null,null], 
   [11015646818.20274,-482410203.4928556,14040634.58841678,-112035.0684939814,null,null,null], 
   ...
 ]"

(in this case, 1 array wrapping 6 arrays of 7 floats/nulls each)

I managed to get results shown on Pic2 using this query:

Pic2:

SELECT FF,
       [0] AS DO0 ,
       [1] AS DO1 ,
       [2] AS DO2 ,
       [3] AS DO3 ,
       [4] AS DO4 ,
       [5] AS DO5 ,
       [6] AS DO6
FROM ( 
    SELECT TOP 36 FF, DO, Value
    FROM MyTable WITH (NOLOCK)
    WHERE Id = 100
    ORDER BY FF, DO
) AS SourceTable 
PIVOT ( 
MAX(value)
    FOR DO IN ([0], [1], [2], [3], [4], [5], [6]) 
) AS PivotTable

Adding FOR JSON AUTO I get this result, which is the closer I came to the desired result:

"[
  {
    "FF": 0,
    "DO0": 8099996673.580311,
    "DO1": -11940671.04204195
  },
  {
    "FF": 1,
    "DO0": 10575727464.91492,
    "DO1": -233647906.0869318,
    "DO2": 3359200.530983179
  },
  {
    "FF": 2,
    "DO0": 11693775141.49235,
    "DO1": -429492903.0961588,
    "DO2": 10721235.27578629,
    "DO3": -74363.98732124352
  },
  ...
]"

Could anyone give me some pointers? Thanks a lot.

Edit: Adding sample data:

|  FF |      D0 |      D1 |         D2 |       D3 |      D4 |    D5 |   D6 |
|:---:|--------:|--------:|-----------:|---------:|--------:|------:|-----:|
|  0  |  809973 |   -1191 |       NULL |     NULL |    NULL |  NULL | NULL |
|  1  | 1057564 |  -23366 |    3359200 |     NULL |    NULL |  NULL | NULL |
|  2  | 1169341 |  -42943 |    1079235 |   -74363 |    NULL |  NULL | NULL |
|  3  | 1071256 |  -15127 |   -7915416 |   362620 |   -3310 |  NULL | NULL |
|  4  | 1409215 | -153899 |   13408335 | -5336555 |   93451 |  -586 | NULL |
|  5  | -328619 |  804878 | -125937545 |   774136 | -226559 | 30247 | -155 |

Unfortunately this is far from trivial, even with `FOR JSON`, because there's no support for generating scalar arrays, so you're looking at [ugly string concatenation solutions](https://stackoverflow.com/q/37708638/4137916), which are made only slightly better if you have SQL Server 2017+ and `STRING_AGG`. — Jeroen Mostert, Dec 04 '19 at 12:22
If you post your sample data as text, then you have get some answers here, but otherwise you're expecting us to transcribe your data; which is very unlikely to happen. — Thom A, Dec 04 '19 at 14:04
@Larnu you're right, sorry - edited the post and added some sample data — jelito, Dec 04 '19 at 15:30
What determines the "order" of your sample data? I can't see an `ORDER BY` that retains it (apart from `ORDER BY D6, D5, D4, D3, D2, ABS(D1)`, but I doubt that's more than coincidence). — Thom A, Dec 04 '19 at 15:51
Added the `ORDER BY` which I deleted by mistake when editting my post. — jelito, Dec 04 '19 at 16:18
I came up with a not very elegant solution by using string manipulation, following @JeroenMostert advice. I added it to the post. Thanks! — jelito, Dec 04 '19 at 16:21

score 0 · Answer 1 · answered Dec 05 '19 at 13:02

Edit 2: This is a solution I found:

DECLARE @json NVARCHAR(MAX) = (
        SELECT [0] AS D0
                 , [1] AS D1
                 , [2] AS D2
                 , [3] AS D3
                 , [4] AS D4
                 , [5] AS D5
                 , [6] AS D6

        FROM (
            SELECT TOP 36 FF, DO, Value
            FROM MyTable WITH (NOLOCK)
            WHERE Id = @Id
      ORDER BY FF, DO
        ) AS SourceTable
        pivot
        (
            MAX(value)
            FOR DO IN ([0], [1], [2], [3], [4], [5], [6])
        ) AS PivotTable for JSON AUTO
);

set @json = (SELECT REPLACE((select @json as json), '{', '['));
set @json = (SELECT REPLACE((select @json as json), '}', ']'));

set @json = (SELECT REPLACE((select @json as json), '"D0":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D1":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D2":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D3":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D4":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D5":', ''));
set @json = (SELECT REPLACE((select @json as json), '"D6":', ''));

RETURN @json;

Result:

[
  [
    8099996673.580311,
    -11940671.04204195
  ],
  [
    10575727464.91492,
    -233647906.0869318,
    3359200.530983179
  ],
  [
    11693775141.49235,
    -429492903.0961588,
    10721235.27578629,
    -74363.98732124352
  ],
  [
    10712544156.84927,
    -151227127.4954886,
    -7915416.297312453,
    362620.9659495770,
    -3310.492070233489
  ],
  [
    14092371615.84298,
    -1538992059.857372,
    134808335.5258479,
    -5336555.181154305,
    93451.37357258648,
    -586.4355493504229
  ],
  [
    -3286355419.227318,
    8048710298.354312,
    -1251937545.910397,
    77054136.25657171,
    -2226559.780061883,
    30247.00533798033,
    -155.7244489259102
  ]
]

Thanks @JeroenMostert for the tip!

How to return a nested JSON array

1 Answers1