CODEWITHSUNDEEP
sqlarraysjsonsql-servert-sql
gryuarthurlend5
gryuarthurlend5

Reputation: 9

Return multiple rows using the following SQL Server TSQL from JSON

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?

Upvotes: 0

Views: 60

Answers (1)

GMB
GMB

Reputation: 222422

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

Upvotes: 3

Related Questions