Question 90101 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search