Extract JSON Object from Json Array into mysql 8

Question

["{"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}

Salman Arshad · Accepted Answer · 2021-10-07 11:16:55Z

1

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

edited Oct 7, 2021 at 11:16

answered Oct 7, 2021 at 9:50

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SYED MUSTAFA HUSSAIN Over a year ago

THANK YOU ! for your quick response .Sorry ,I have some changes into Json like ["{\"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}"]

SYED MUSTAFA HUSSAIN Over a year ago

now how can i achieve this and I want to return just result ?

Salman Arshad Over a year ago

It is not difficult to change the column names in the query I posted.

SYED MUSTAFA HUSSAIN Over a year ago

No, my question is something else I knew changing column name is not difficult but thank you !!

SYED MUSTAFA HUSSAIN Over a year ago

my question is that now I have a JSON array but JsonObject acts as a string type, now I cannot extract data due to string type so that's my question

|

Collectives™ on Stack Overflow

Extract JSON Object from Json Array into mysql 8

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related