Extract specific objects from json array mysql

Asked Feb 01 '19 at 10:12

Active Feb 01 '19 at 11:35

Viewed 281 times

I have a JSON column 'cart_data' with following data :

[{
    "type": 2,
    "amount": 500
}, {
    "type": 1,
    "amount": 600
},
, {
    "type": 1,
    "amount": 700
}]

I want to fetch cart_data column which contains type 1 and output should be :

[{
    "type": 1,
    "amount": 600
},
{
    "type": 1,
    "amount": 700
}]

Basically I want to remove objects which has type other than 1 (in this example).

Here's what I've been trying :

SELECT  order_id , 
        json_remove(json_extract(cart_data, '$[*]'),'$[0]') as cart_data 
from billing 
where json_contains(cart_data->'$[*].type', json_array(1));

In above query, 2nd parameter for json_remove is static. I want to make it dynamic to remove all objects which doesn't have type : 1

Any possibilities?

Thanks.

edited Feb 01 '19 at 10:34

asked Feb 01 '19 at 10:12

AkKi

@RiggsFolly , This is not relevant to my question. Please remove duplicate mark. I am looking for a mysql solution! – AkKi Feb 01 '19 at 10:20
WHat version of MySQL are you using – RiggsFolly Feb 01 '19 at 10:23
@RiggsFolly, I am using v5.7 – AkKi Feb 01 '19 at 10:23
Edited, please check now. – AkKi Feb 01 '19 at 10:30
Thanks @RiggsFolly – AkKi Feb 01 '19 at 10:34
All you had to do was show you had attempted something, so the question did not look like a DIFM – RiggsFolly Feb 01 '19 at 10:35
Yes, understood. – AkKi Feb 01 '19 at 10:36

Extract specific objects from json array mysql

0 Answers0