Return multiple rows using the following SQL Server TSQL from JSON

Question

I am trying to return multiple rows using the following SQL Server 2017 TSQL from JSON

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "DateCreated": "2020-08-02T16:04:59.3558001+01:00",
  "Name": "Bolts",
  "Price": 123.45,
  "Length": 15.7,
  "Quantity": 1,
  "Attributes": [
    {
      "AttrHeading": "Material",
      "AttrVal": "Steel"
    },
    {
      "AttrHeading": "Colour",
      "AttrVal": "Black"
    },
    {
      "AttrHeading": "Finish",
      "AttrVal": "Matt"
    }
  ]
}';

SELECT j.*
FROM OPENJSON (@json, N'$')
  WITH (
    
    DateCreated datetime2 N'$.DateCreated',
    [Name] varchar(100) N'$.Name',
    Price decimal(19,4) N'$.Price',
    [Length] decimal(19,4) N'$.Length',
    Quantity integer N'$.Quantity',
    AttrHeading varchar(max) N'$.Attributes.AttrHeading',
    AttrVal varchar(max) N'$.Attributes.AttrVal'
  ) AS j;

What I need is for three rows to be returned, one row for each of the Attributes. So the Name, Price, Length etc would be the same on each row, just AttrHeading and AttrValue would change.

Currently AttrHeading and AttrVal are null - is it possible to do this?

GMB · Accepted Answer · 2020-08-30 20:52:52Z

You need to OPENJSON() twice: once for the outer object (at that time, the nested array should go to a JSON column), and a second time to unnest the array:

SELECT j.DateCreated, j.Name, j.Price, j.Length, j.Quantity, x.*
FROM OPENJSON (@json) WITH (
    DateCreated datetime2,
    Name        varchar(100),
    Price       decimal(19,4),
    Length      decimal(19,4),
    Quantity    integer,
    Attributes  nvarchar(max) AS JSON
) AS j
OUTER APPLY OPENJSON(j.Attributes) WITH (
    AttrHeading varchar(max),
    AttrVal     varchar(max)
) x;

Note that I shortened the query a little, by removing redondent column names and paths.

Demo on DB Fiddle:

DateCreated                 | Name  |    Price |  Length | Quantity | AttrHeading | AttrVal
:-------------------------- | :---- | -------: | ------: | -------: | :---------- | :------
2020-08-02 16:04:59.3558001 | Bolts | 123.4500 | 15.7000 |        1 | Material    | Steel  
2020-08-02 16:04:59.3558001 | Bolts | 123.4500 | 15.7000 |        1 | Colour      | Black  
2020-08-02 16:04:59.3558001 | Bolts | 123.4500 | 15.7000 |        1 | Finish      | Matt

Collectives™ on Stack Overflow

Return multiple rows using the following SQL Server TSQL from JSON

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related