Extract JSON Object from Json Array into mysql 8-CodePudding

["{"delivered":0,"deliveryDate":1633537686480,"toUserId":148}","{"delivered":0,"deliveryDate":1633537687590,"toUserId":226}","{"delivered":1,"deliveryDate":1633537687741,"toUserId":160}","{"delivered":0,"deliveryDate":1633537687863,"toUserId":262}","{"delivered":0,"deliveryDate":1633537688019,"toUserId":263}","{"delivered":0,"deliveryDate":1633537688174,"toUserId":264}","{"delivered":0,"deliveryDate":1633537688325,"toUserId":265}"]

I want to extract this JSON Array from the below table then further I want to extract the JSON object in which I give userId and deliveryStatus .How can I achieve this?

expected result when i give toUserid 148 and also given deliveryStatus= 0

{{"delivered":0,"deliveryDate":1633537686480,"toUserId":148}

CodePudding user response：

You can use JSON_TABLE function to convert JSON into rows; then filter using -> operator:

SELECT t.id, JSON_PRETTY(j.obj)
FROM t
CROSS JOIN JSON_TABLE(t.delivery_status, '$[*]' COLUMNS(
  obj JSON PATH '$'
)) AS j
WHERE j.obj->'$.delivered' = 0 AND j.obj->'$.toUserId' = 148