1

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?

user2864740
  • 60,010
  • 15
  • 145
  • 220
Pouya
  • 114
  • 1
  • 8
  • 36

2 Answers2

1

I have created similar carts table and was able to replicate your error:

enter image description here

This should find the value

SELECT JSON_UNQUOTE(JSON_EXTRACT(crt_content, '$[0].id')) as scope from carts

enter image description here

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
Mauricio
  • 473
  • 5
  • 11
  • With expressions similar to the last one you would get `ERROR 3143 (42000): Invalid JSON path expression`; see more details [here](https://stackoverflow.com/questions/41958183/mysql-5-7-error-3143-42000-invalid-json-path-expression-the-error-is-around) – Mauricio Mar 16 '23 at 21:41
0

You can try LIKE query, SELECT * from carts WHERE crt_content LIKE '%"id":"24"%';

Anisur Rahman
  • 644
  • 1
  • 4
  • 17
  • It is quite brittle to use string operations on JSON content. – user2864740 Jan 30 '22 at 06:42
  • 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 04 '22 at 04:01