Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a table named carts which has this structure:

1   crt_id Primary  bigint(20)      
2   crt_mbr_id      bigint(20)          
3   crt_session_id  varchar(128)    
4   crt_content     text    
5   crt_send_type   int(11)         
6   crt_completed   tinyint(1)          
7   created_at      timestamp           
8   updated_at      timestamp   
And crt_content data goes like this:
[{"id":"24","quantity":"1","price":3000,"discounted":3000,"coupon":0}]
Now I need to search in crt_content for the number 24.
So I tried this:
SELECT JSON_UNQUOTE(JSON_EXTRACT(crt_content, '24')) as scope from carts
But this will give me this error:
#3143 - Invalid JSON path expression. The error is around character position 1
So what's going wrong here? How can I search properly for id of 24 in crt_content field of this carts table?
                Also referring to the documentation can reduce random attempts: dev.mysql.com/doc/refman/8.0/en/json-search-functions.html
– user2864740
                Jan 30, 2022 at 6:37
                This is a poor answer and should be downvoted. It doesn't address the point of the question which is the manipulation of JSON data and addressing the MySQL error. Reverting back in time and functionality to LIKE % queries would work, but isn't the point of the question.
– JimmyDix
                Aug 4, 2022 at 4:01
I have created similar carts table and was able to replicate your error:
This should find the value
SELECT JSON_UNQUOTE(JSON_EXTRACT(crt_content, '$[0].id')) as scope from carts
But if you need to use WHERE condition, do it like this:
select * 
from carts 
where JSON_UNQUOTE(JSON_EXTRACT(crt_content, '$[0].id')) = 24
If content were not a JSON ARRAY like {"id":"24","quantity":"1","price":3000,"discounted":3000,"coupon":0} then you would use:
select * 
from carts 
where JSON_UNQUOTE(JSON_EXTRACT(crt_content, '$.id')) = 24
If you know JSON PATH would not exist, that error may be thrown, so have to use double quotes like this:
select * 
from carts 
where JSON_UNQUOTE(JSON_EXTRACT(crt_content, '$."uncertain"."path"."id"')) = 24
                With expressions similar to the last one you would get ERROR 3143 (42000): Invalid JSON path expression; see more details here
– Mauricio
                Mar 16 at 21:41
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.