Using T-SQL to retrieve results from Json file and not iterating multiple sub-objects

Question

Following MS documentation, I can get a simple example of loading json file to SQL results. The problems occur when I have more than one sub-object. This code will traverse all elements if at root level. Because I have 2 objects under "Purchase" I have to explicitly reference them. Is there an easier way to return results for all sub-objects? In this case I would like two rows of Order info.

Also have to hard code the filename to OPENROWSET instead of using (@file). Any ideas on syntax to pass in a variable for file?

Code

USE TempDB

DECLARE @json AS NVARCHAR(MAX)
DECLARE @file AS NVARCHAR(MAX)

SET @file = 'c:\temp\test.json';
SELECT @json = BulkColumn FROM OPENROWSET (BULK 'c:\temp\test2.json', SINGLE_CLOB) AS j

SELECT * 
FROM OPENJSON ( @json )  
WITH (   
    Number   varchar(200) '$.Purchase[0].Order.Number' ,  
    Date     datetime     '$.Purchase[0].Order.Date',  
    Customer varchar(200) '$.Purchase[0].AccountNumber',  
    Quantity int          '$.Purchase[0].Item.Quantity'  
 )

File contents:

{
    "Purchase": [
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2011-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2011-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]
}

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017#option-2---openjson-output-with-an-explicit-structure

Thanks, Bill

Razvan Socol · Accepted Answer · 2019-06-15 13:22:51Z

1

To get both rows, you need to use the second argument of the OPENJSON function, like this:

SELECT * 
FROM OPENJSON ( @json,'$.Purchase' )  
WITH (   
    Number   varchar(200) '$.Order.Number' ,  
    Date     datetime     '$.Order.Date',  
    Customer varchar(200) '$.AccountNumber',  
    Quantity int          '$.Item.Quantity'  
 )

This way you are telling SQL Server that you want all the nodes under the '$.Purchase' path (and it finds two rows). Without that, you would get all the nodes under root (and it finds just one row, the Purchase node).

answered Jun 15, 2019 at 13:22

Razvan Socol

5,7592 gold badges22 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Using T-SQL to retrieve results from Json file and not iterating multiple sub-objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related