Extracting the values of a nested array in JSON using SQL Server

Question

I'm trying to extract the values of a nested array inside JSON in SQL Server but not sure how. I know how to extract JSON items and also how to extract arrays but haven't been able to get the nested array out values. I found one example that supposedly shows how to do this but haven't been able to get it to work.

Here's an example: The following JSON falls under a field called json_data. There is an array under 'order', called 'line_items'. Each item in 'line_items' has multiple fields: 'quantity', 'name', 'total_money', etc.

Extracting the top level variables: 'order', 'id' is easy. But not sure how to go about extracting all the variables under the array 'line_items': specifically, 'quantity', 'name', 'total_money'. I would be grateful for any help. Many thanks!

{'order': {'location_id': 'NYC',
'id': '123',
'source': {'name': 'Kurtz'},
'line_items': [{'quantity': '1',
'name': 'Item1',
'note': 'No notes',
'base_price_money': {'amount': 1000, 'currency': 'USD'},
'total_discount_money': {'amount': 0, 'currency': 'USD'},
'total_money': {'amount': 1000, 'currency': 'USD'}},
{'quantity': '1',
'name': 'Item2',
'note': '',
'base_price_money': {'amount': 400, 'currency': 'USD'},
'gross_sales_money': {'amount': 400, 'currency': 'USD'},
'total_tax_money': {'amount': 0, 'currency': 'USD'},
'total_discount_money': {'amount': 0, 'currency': 'USD'},
'total_money': {'amount': 400, 'currency': 'USD'}}],
'total_money': {'amount': 1400, 'currency': 'USD'},
'total_tax_money': {'amount': 0, 'currency': 'USD'},
'total_discount_money': {'amount': 0, 'currency': 'USD'}},
'location': {'name': 'Breakfast Club',
'address': 'Buena Vista road',
'phone': '555-555-5155'}}

Yitzhak Khabinsky · Accepted Answer · 2021-08-12 15:41:21Z

2

Assuming that you have SQL Server 2016 or later.

SQL

DECLARE @json NVARCHAR(MAX) = 
N'{
    "order": {
        "location_id": "NYC",
        "id": "123",
        "source": {
            "name": "Kurtz"
        },
        "line_items": [
            {
                "quantity": "1",
                "name": "Item1",
                "note": "No notes",
                "base_price_money": {
                    "amount": 1000,
                    "currency": "USD"
                },
                "total_discount_money": {
                    "amount": 0,
                    "currency": "USD"
                },
                "total_money": {
                    "amount": 1000,
                    "currency": "USD"
                }
            },
            {
                "quantity": "1",
                "name": "Item2",
                "note": "",
                "base_price_money": {
                    "amount": 400,
                    "currency": "USD"
                },
                "gross_sales_money": {
                    "amount": 400,
                    "currency": "USD"
                },
                "total_tax_money": {
                    "amount": 0,
                    "currency": "USD"
                },
                "total_discount_money": {
                    "amount": 0,
                    "currency": "USD"
                },
                "total_money": {
                    "amount": 400,
                    "currency": "USD"
                }
            }
        ],
        "total_money": {
            "amount": 1400,
            "currency": "USD"
        },
        "total_tax_money": {
            "amount": 0,
            "currency": "USD"
        },
        "total_discount_money": {
            "amount": 0,
            "currency": "USD"
        }
    },
    "location": {
        "name": "Breakfast Club",
        "address": "Buena Vista road",
        "phone": "555-555-5155"
    }
}';

--SELECT ISJSON(@json);

SELECT --parent.*
    parent.id, parent.[location_id], parent.[source]
   , child.*
FROM OPENJSON(@json, '$.order') 
WITH
(
   id      INT         '$.id',
   [location_id]   VARCHAR(30) '$.location_id',
   [source]   VARCHAR(30) '$.source.name',
   [line_items]   NVARCHAR(MAX) AS JSON
)
AS parent
CROSS APPLY OPENJSON(parent.[line_items])
WITH
(
   quantity         INT         '$.quantity',
   [name]           VARCHAR(50) '$.name',
   [note]           VARCHAR(50) '$.note',
   [base_price_money.amount]            VARCHAR(50) '$.base_price_money.amount',
   [base_price_money.currency]          VARCHAR(50) '$.base_price_money.currency'
) AS child;

Output

+-----+-------------+--------+----------+-------+----------+-------------------------+---------------------------+
| id  | location_id | source | quantity | name  |   note   | base_price_money.amount | base_price_money.currency |
+-----+-------------+--------+----------+-------+----------+-------------------------+---------------------------+
| 123 | NYC         | Kurtz  |        1 | Item1 | No notes |                    1000 | USD                       |
| 123 | NYC         | Kurtz  |        1 | Item2 |          |                     400 | USD                       |
+-----+-------------+--------+----------+-------+----------+-------------------------+---------------------------+

answered Aug 12, 2021 at 15:41

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

4 Comments

J. Jonah Jameson Over a year ago

Thanks, I'm not sure we have SQL Server 2016 but I will try this - much appreciated!

Yitzhak Khabinsky Over a year ago

@J.JonahJameson, just execute SELECT @@VERSION; to find out.

J. Jonah Jameson Over a year ago

I got: Microsoft SQL Azure (RTM) - 12.0.2000.8

Yitzhak Khabinsky Over a year ago

@J.JonahJameson, in such case it should work. Microsoft SQL Azure is using a different naming scheme.

lptr · Accepted Answer · 2021-08-12 15:12:29Z

…

declare @j nvarchar(max) = N'{"order": {"location_id": "NYC",
"id": "123",
"source": {"name": "Kurtz"},
"line_items": [{"quantity": "1",
"name": "Item1",
"note": "No notes",
"base_price_money": {"amount": 1000, "currency": "USD"},
"total_discount_money": {"amount": 0, "currency": "USD"},
"total_money": {"amount": 1000, "currency": "USD"}},
{"quantity": "2",
"name": "Item2",
"note": "",
"base_price_money": {"amount": 400, "currency": "USD"},
"gross_sales_money": {"amount": 400, "currency": "USD"},
"total_tax_money": {"amount": 0, "currency": "USD"},
"total_discount_money": {"amount": 0, "currency": "USD"},
"total_money": {"amount": 400, "currency": "USD"}}],
"total_money": {"amount": 1400, "currency": "USD"},
"total_tax_money": {"amount": 0, "currency": "USD"},
"total_discount_money": {"amount": 0, "currency": "USD"}},
"location": {"name": "Breakfast Club",
"address": "Buena Vista road",
"phone": "555-555-5155"}}';


select *
from openjson(@j, '$.order.line_items')
with
(
--..adjust datatypes..
quantity int,
name nvarchar(50),
note nvarchar(50),
basepriceamount money '$.base_price_money.amount',
basepricecurrency nvarchar(10) '$.base_price_money.currency',
totaldiscountamount money '$.total_discount_money.amount',
totaldiscountcurrency nvarchar(10) '$.total_discount_money.currency',
totalmoneyamount money '$.total_money.amount',
totalmoneycurrency nvarchar(10) '$.total_money.currency'
);

Collectives™ on Stack Overflow

Extracting the values of a nested array in JSON using SQL Server

2 Answers 2

4 Comments

1 Comment

Your Answer

Collectives™ on Stack Overflow

Extracting the values of a nested array in JSON using SQL Server

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest