Select JSON values from a SQL table and convert the result into a single JSON object

Question

I've searched high and low but can't seem to find anything even remotely like what I'm trying to do but I'm sure this isn't anything new and I'm quite sure I'm having a Homer moment.

Given a table that contains a JSON column:

DECLARE @Table TABLE
(
    [VALUE] nvarchar(max) NOT NULL
);

The value within the column can be any valid JSON OBJECT, I don't have control over the data, only it's validity and that all the entries are of the same structure but which I don't know. This is important as it does restrict certain available options.

INSERT INTO @Table
VALUES ( N'{"X":"2020-01-01","Y":"27"}' )
, ( N'{"X":"2020-02-01","Y":"48"}' )
, ( N'{"X":"2020-04-01","Y":"63"}' )
, ( N'{"X":"2020-05-01","Y":"75"}' )
, ( N'{"X":"2020-06-01","Y":"32"}' )
, ( N'{"X":"2020-08-01","Y":"12"}' )
, ( N'{"X":"2020-09-01","Y":"96"}' )
, ( N'{"X":"2020-10-01","Y":"105"}' );

INSERT INTO @Table
VALUES ( N'{"Monkey":"1","Elephant":"9"}' )
, ( N'{"Monkey":"2","Elephant":"8"}' )
, ( N'{"Monkey":"3","Elephant":"7"}' )
, ( N'{"Monkey":"4","Elephant":"6"}' )
, ( N'{"Monkey":"5","Elephant":"5"}' )
, ( N'{"Monkey":"6","Elephant":"4"}' )
, ( N'{"Monkey":"7","Elephant":"3"}' )
, ( N'{"Monkey":"8","Elephant":"2"}' );

I need to be able to convert the contents of all the rows into a single JSON Document:

[{
    "VALUES": [
        { "X": "2020-01-01", "Y": "27" },
        { "X": "2020-02-01", "Y": "48" },
        { "X": "2020-04-01", "Y": "63" },
        { "X": "2020-05-01", "Y": "75" },
        { "X": "2020-06-01", "Y": "32" },
        { "X": "2020-08-01", "Y": "12" },
        { "X": "2020-09-01", "Y": "96" },
        { "X": "2020-10-01", "Y": "105" }
    ]
}]

Given my SELECT statement, I'm able to at least query the data into a table result:

SELECT [VALUES] = JSON_QUERY( [VALUE], N'$' )
FROM @Table;

VALUES
{"X":"2020-01-01","Y":"27"}
{"X":"2020-02-01","Y":"48"}
{"X":"2020-04-01","Y":"63"}
{"X":"2020-05-01","Y":"75"}
{"X":"2020-06-01","Y":"32"}
{"X":"2020-08-01","Y":"12"}
{"X":"2020-09-01","Y":"96"}
{"X":"2020-10-01","Y":"105"}

But when I convert the result to JSON it goes awry:

SELECT [VALUES] = JSON_QUERY( [VALUE], N'$' )
FROM @Table
FOR JSON PATH;

[
    { "VALUES": { "X": "2020-01-01", "Y": "27" } },
    { "VALUES": { "X": "2020-02-01", "Y": "48" } },
    { "VALUES": { "X": "2020-04-01", "Y": "63" } },
    { "VALUES": { "X": "2020-05-01", "Y": "75" } },
    { "VALUES": { "X": "2020-06-01", "Y": "32" } },
    { "VALUES": { "X": "2020-08-01", "Y": "12" } },
    { "VALUES": { "X": "2020-09-01", "Y": "96" } },
    { "VALUES": { "X": "2020-10-01", "Y": "105" } }
]

For the life of me I just can't seem to get the last bit sorted, hopefully another set of smarter eyes will spot my mistake.

Sander · Accepted Answer · 2020-09-03 09:55:09Z

1

If you can guarantee that the table contains valid JSON, then below is a quick and dirty solution. Not using any Microsoft SQL Server JSON support, but some string concatenation...

-- SQL SERVER 2016
select '[{ "VALUES":['
    +  stuff((  select ',' + t.[VALUE]
                from @Table t
                for xml path(''), type).value('.', 'nvarchar(max)'), 1,1,'')
    + ']}]';


-- SQL SERVER 2017 and later
select '[{ "VALUES":[' + string_agg(t.VALUE, ',') + ']}]' from @Table t;

Formatted result for the first set of sample data:

[
  {
    "VALUES": [
      {
        "X": "2020-01-01",
        "Y": "27"
      },
      {
        "X": "2020-02-01",
        "Y": "48"
      },
      {
        "X": "2020-04-01",
        "Y": "63"
      },
      {
        "X": "2020-05-01",
        "Y": "75"
      },
      {
        "X": "2020-06-01",
        "Y": "32"
      },
      {
        "X": "2020-08-01",
        "Y": "12"
      },
      {
        "X": "2020-09-01",
        "Y": "96"
      },
      {
        "X": "2020-10-01",
        "Y": "105"
      }
    ]
  }
]

answered Sep 3, 2020 at 9:55

Sander

4,0822 gold badges21 silver badges23 bronze badges

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

2 Comments

Storm Over a year ago

Thanks for this, I should have mentioned that I know that you can do a string manipulation to get it but that just feels so hacky for something that I'm sure should be perfectly doable in the base statement. I'm convinced I'm just missing one tiny thing.

Storm Over a year ago

Ok, so I feel like a right Homer. I'll admit I only glanced at your solution, saw the STUFF and stopped there. I took a break, came back, sat down and figured that I should use an XML converter to compress the separate rows into one because after staring at the screen, I realised the rows were "actually" tables of single rows. Then I figured I'd have to use a STUFF to get comma's in and thought hmm, Sander used a STUFF, let's just take a second look and would you believe it, my code looks like yours :P

Collectives™ on Stack Overflow

Select JSON values from a SQL table and convert the result into a single JSON object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related