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

Postgresql – Using JSON value as IN operator in Postgres SQL

I’ve two tables like below

Table 1 : users

id – number

authorization – json

id user_name authorized_customers
1 user1 ["002001A", "002001B","002001ABC"]
2 user2 ["002001A", "002001Z","002001ABZ"]

Table 2: orders

id – number,

user_id – number – forigen key of users table

name – varchar

customer – varchar

id customer_name customer
1 john 002001A
2 doe 002001Z

My question is:

How do I get all orders based on the user1’s authorized customer like

select * from orders where customer in(select CAST(json_array_elements(authorized_customers) AS VARCHAR) AS customer_ids from users where id=1)

Any help is appreciated.

I tired above query but no value returned

Tags:

3

Answers


  1. 0

    Postgres supports a number of operators for dealing with JSON; in this case you can use the json ? text -> boolean operator as described in the official docs.

    Something like:

    select * 
from orders o
join users u on u.authorized_customers ? o.customer
where u.id = 1

    will return records where the o.customer value is in the JSON array u.authorized_customers.

    Login or Signup to reply.
  2. 0

    you can try this

    SELECT * FROM orders
WHERE customer IN (
    SELECT json_array_elements_text(authorized_customers) AS customer_ids 
    FROM users 
    WHERE id = 1
)

    make sure that you have db indexes on users.id and orders.customer columns. It can greatly improve the performance.
    How does json_array_elements_text work?:
    example users data:

    id | user_name | authorized_customers
-----------------------------------------
1  | user1     | ["002001A", "002001B","002001ABC"]
2  | user2     | ["002001A", "002001Z","002001ABZ"]

    query like:

    SELECT json_array_elements_text(authorized_customers) FROM users WHERE id = 1;

    will make this output:

    json_array_elements_text
--------------------------
002001A
002001B
002001ABC
    Login or Signup to reply.
  3. 0

    Go with json_array_elements_text function. It will extract the data from the array.

    Here is how to execute query:

    SELECT * FROM orders o JOIN users u ON o.customer = ANY(json_array_elements_text(u.authorized_customers))
WHERE u.id = 1;

    The join is being performed on basis of

    o.customer = ANY(json_array_elements_text(u.authorized_customers))

    Value is returned if any of the value matches.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search