I'm really struggling to understand the documentation on how to use sp_parse_json for real world examples. I think I must just be misunderstanding something fundamental when it comes to arrays and rows but my results are real hit and miss.

I have managed to parse simple json on two difference occasions, but they are extremely small and flat json files, i.e. one level.

I want to read in a hugely complex HMRC json file, but for now I'm just trying to understand a simple Shopify json file, I would like to know how to just get the shop_id level data as a result set but then also how best to move into the line_items detail

If I try

call "sp_parse_json"('sql_array',myjson);
select ("sql_array"."fulfillment_orders")."shop_id"

I get

Column 'shop_id' not found in variable '(sql_array.fulfillment_orders)'

If I remove the brackets I get

Correlation name 'fulfillment_orders' not found

When I try using sa_rowgenerator it doesn't work either.

This is a sample json file

    "fulfillment_orders": [
            "id": 6430414668089,
            "shop_id": 82390679865,
            "order_id": 5528104436025,
            "assigned_location_id": 91234271545,
            "request_status": "unsubmitted",
            "status": "open",
            "supported_actions": [
            "destination": {
                "id": 6021472715065,
                "address1": "105 Victoria St",
                "address2": null,
                "city": "Toronto",
                "company": "Company Name",
                "country": "Canada",
                "email": "russel.winfield@example.com",
                "first_name": "Russell",
                "last_name": "Winfield",
                "phone": null,
                "province": null,
                "zip": "M5C1N7"
            "line_items": [
                    "id": 14269181100345,
                    "shop_id": 82390679865,
                    "fulfillment_order_id": 6430414668089,
                    "quantity": 1,
                    "line_item_id": 14132477690169,
                    "inventory_item_id": 48774625657145,
                    "fulfillable_quantity": 1,
                    "variant_id": 46726588563769
                    "id": 14269181165881,
                    "shop_id": 82390679865,
                    "fulfillment_order_id": 6430414668089,
                    "quantity": 2,
                    "line_item_id": 14132477722937,
                    "inventory_item_id": 48774625689913,
                    "fulfillable_quantity": 2,
                    "variant_id": 46726588629305
            "international_duties": null,
            "fulfill_at": "2023-08-30T10:00:00-04:00",
            "fulfill_by": null,
            "fulfillment_holds": [],
            "created_at": "2023-08-30T10:28:43-04:00",
            "updated_at": "2023-08-30T10:28:43-04:00",
            "delivery_method": {
                "id": 557449118009,
                "method_type": "shipping",
                "min_delivery_date_time": null,
                "max_delivery_date_time": null
            "assigned_location": {
                "address1": null,
                "address2": null,
                "city": null,
                "country_code": "GB",
                "location_id": 91234271545,
                "name": "Shop location",
                "phone": null,
                "province": null,
                "zip": null
            "merchant_requests": []

If anyone can help this old dog learn new tricks I'd be extremely grateful 😃

asked 26 Oct, 13:47

pcollins's gravatar image

accept rate: 0%

I'm not sure if this is the explanation you're looking for.

In the sample json fulfillment_orders is an array. This means that you can only access the elements in that array using [[n]].

So sql_array.fulfillment_orders[[1]] is the first element in that array. This array element contains all the other properties for example sql_array.fulfillment_orders[[1]].shop_id and sql_array.fulfillment_orders[[1]].destination.city.

In the array sql_array.fulfillment_orders another property array line_items is available. That data can be accessed using the same syntax

sql_array.fulfillment_orders[[1]].line_items[[1]].id    -> 14269181100345
sql_array.fulfillment_orders[[1]].line_items[[2]].id    -> 14269181165881

With cardinality(sql_array.fulfillment_orders) you can determine the number of array elements in the first array. With the sample json this will return 1.

To determine the number of elements in the line_items array you can call cardinality(sql_array.fulfillment_orders[[1]].line_items). With the sample json this will return 2

permanent link

answered 30 Oct, 05:29

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 21%

Thanks that helps

(30 Oct, 09:09) pcollins

I am able to parse it using the answer on this thread


I'm sure I tried it before, but clearly not or I did something wrong


call sp_parse_json ('sql_array', MyJson);

select x.row_num JsonIndex, sql_array.fulfillment_orders[[x.row_num]].shop_id,




from sa_rowgenerator(1, CARDINALITY(sql_array.fulfillment_orders)) as x,

sa_rowgenerator(1, CARDINALITY(sql_array.fulfillment_orders[[JsonIndex]].line_items)) as y,

permanent link

answered 26 Oct, 13:54

pcollins's gravatar image

accept rate: 0%

That's not to say I fully understand what's happening, but at least I can try and reverse it back out to make sense to me

(26 Oct, 15:24) pcollins
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 26 Oct, 13:47

question was seen: 157 times

last updated: 30 Oct, 09:09