Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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": [
                "create_fulfillment",
                "hold",
                "split"
            ],
            "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 '23, 13:47

pcollins's gravatar image

pcollins
1913612
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 '23, 05:29

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354765
accept rate: 21%

Thanks that helps

(30 Oct '23, 09:09) pcollins

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

https://sqlanywhere-forum.sap.com/questions/36075/nested-arrays-in-sa_rowgenerator-with-sp_parse_json

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

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

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

sql_array.fulfillment_orders[[x.row_num]].assigned_location_id,

((sql_array.fulfillment_orders[[JsonIndex]]).line_items[[y.row_num]]).quantity,

((sql_array.fulfillment_orders[[JsonIndex]]).line_items[[y.row_num]]).inventory_item_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,

end;
permanent link

answered 26 Oct '23, 13:54

pcollins's gravatar image

pcollins
1913612
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 '23, 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

By RSS:

Answers

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:

×30

question asked: 26 Oct '23, 13:47

question was seen: 286 times

last updated: 30 Oct '23, 09:09