sql how to select row based on json format field value?

Question

I have a table ,one of the table filed is json format:

my_table

id  json_field

1   { "to_status": 7, "to_status_name": "In Progress", "role": "admin"}

2   { "to_status": 3, "to_status_name": "Completed", "role": "admin"}

3   { "to_status": 2, "to_status_name": "Completed", "role": "customer"}

How can I select all rows that "to_status" is 3 or 2 ?

Any friend can help?

Nysand · Accepted Answer · 2023-01-17 17:11:36Z

2

You can use SELECT * FROM my_table WHERE JSON_EXTRACT(json_field, '$.to_status') IN (3, 2)

or

SELECT * FROM my_table WHERE json_field->>'$.to_status' IN (3, 2)

to select what you need.

answered Jan 17, 2023 at 17:11

Nysand

1028 bronze badges

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

2 Comments

William Over a year ago

Thank you for your reply ,I have tired both of them ,none of them work for my case.

Nysand Over a year ago

Try this one too. SELECT * FROM my_table WHERE json_field->>'to_status' = '3' OR json_field->>'to_status' = '2'

Belayer · Accepted Answer · 2023-01-18 20:15:25Z

1

When extracted Json values are strings. So you need to test for string values or cast to_status extracted values to an integer:

SELECT * FROM my_table WHERE json_field->>'to_status' in ('2','3');
or
SELECT * FROM my_table WHERE (json_field->>'to_status')::int in (2,3);

But then you can get the same results extracting the to_status_name:

SELECT * FROM my_table WHERE json_field->>'to_status_name' = 'Completed';

answered Jan 18, 2023 at 20:15

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Collectives™ on Stack Overflow

sql how to select row based on json format field value?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related