CODEWITHSUNDEEP
jsonsql-servert-sqljson.net
Bill
Bill

Reputation: 1205

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

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

Upvotes: 1

Views: 301

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

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).

Upvotes: 1

Related Questions