Extracting a field info from multiple json objects in an array

[sohamjadhav17-1]

Hi,

I am using Snowflake in Looker. I have a Items column which has multiple JSON objects inside an array. I need to extract only the information in 'quantity' json field i.e., i need to extract the quantity number 1 from both the quantity and return as their sum 2 in dimension ”total quantity”. Please help me solve it. The array looks like follow and I have bold and underlined the quantity field:

[ { "_id": "604f89939ae0290014759906", "blurHash": "yAT9L#~q.8~qD%_3%M_3j[aej[ofofay-;j[IAWB%Mt7In?bj[t7ofRjfkofM|ayt7j[Rjayt7~qofM{fQt7ofRjWBayofj[ayayj[", "brand": "Foxybae Hair", "color": "Multi", "description": "Cool AF Heat Protectant Spray", "flipMargin": 0.01, "item": "60131c90230f1e0015b7050c", "keptQuantity": 1, "price": 15, "quantity": 1, "returned": false, "size": "OS", "sizelessSku": "110136792MUL", "sku": "110136792MUL8", "status": "kept" }, { "_id": "604f89939ae0290014759907", "blurHash": "yfQ]yi%M_NxuaK%3IUjJj[t7a{j[j[ae~qay9Fj[W;a{xu-;ayRQoLWBayogRia{oNoLt6ayRjtRj[jYj[kCfifPM{j[xtWVRjj[oL", "brand": "Moonshot", "color": "Beige", "description": "Micro Correctfit Cushion #301 Honey", "flipMargin": 0.01, "item": "6026f958137e64001454e87b", "keptQuantity": 1, "price": 22.5, "quantity": 1, "returned": false, "size": "OS", "sizelessSku": "070120761BEI", "sku": "070120761BEI052", "status": "kept" } ]

[warroso]

not sure if this is helpful, but found a page on Postgres and JSON with the Looker function json_object_keys. I also have an jsonb type field with a json array and json key-values i’m trying to query on.