| id | value |
|---|---|
| 1 | {"customer_name":"John Doe","email":"[email protected]","cart":"[{" product_id":17," description":" Tshirt"," price":50}]", } |
I have data above
when i used this query, it always return as null
select value->>"$.cart[*].product_id" from orders
and
SELECT JSON_EXTRACT(value, '$.cart[*].product_id') from orders
how to write the proper query to get product_id from cart array?
What you posted is not valid JSON. I've marked the problems with 1,2,3 below.
{...,"cart":"[{" product_id":17," description":" Tshirt"," price":50}]", }
1 23
For 1 and 2, you shouldn't put the array in double-quotes. Double-quotes are for strings. It would be okay if you intended cart to be a string with content that happens to resemble a JSON array, but there are double-quote characters inside that string, so it makes the syntax confused.
For 3, you must not have a trailing , in JSON without another field or element following it.
The fixed JSON looks like this:
{"customer_name":"John Doe","email":"[email protected]","cart":[{" product_id":17," description":" Tshirt"," price":50}] }
If you fix those problem, the next problem is that you have spaces in some field names. You should be able to do this:
select value->>'$.cart[*].product_id' ...
But you can't, because the field name isn't "product_id", it's " product_id" (with a leading space).
JSON does allow field names to contain spaces. But if you use field names with spaces, you must query it like this:
select value->>'$.cart[*]." product_id"' ...
But I would recommend you fix the spaces to make it easier to query. Like this:
{"customer_name":"John Doe","email":"[email protected]","cart":[{"product_id":17,"description":"Tshirt","price":50}] }
