|
I'm really struggling to understand the documentation on how to use 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 😃 |
|
I'm not sure if this is the explanation you're looking for. In the sample json So In the array sql_array.fulfillment_orders[[1]].line_items[[1]].id -> 14269181100345 sql_array.fulfillment_orders[[1]].line_items[[2]].id -> 14269181165881 With To determine the number of elements in the Thanks that helps
(30 Oct '23, 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 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;
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
|