Skip to content

Instantly share code, notes, and snippets.

@filipedfs

filipedfs/query_postgres_jsonb.sql

Last active October 25, 2021 19:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save filipedfs/a3661d1a91694740bd4aaf14203f8ae9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save filipedfs/a3661d1a91694740bd4aaf14203f8ae9 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres query to search inside jsonb
Raw
query_postgres_jsonb.sql
-- {"contacts":[{"phoneNumber":987654321}]} See: https://www.postgresql.org/docs/12/functions-json.html
SELECT contacts
FROM customer
WHERE contacts @? '$[*] ? (@.phoneNumber == 987654321)';
-- Other way
WITH data AS (
SELECT jsonb_array_elements(contacts)->>'phoneNumber' "phoneNumber"
FROM customer
)
SELECT * FROM data WHERE "phoneNumber" = '11985594060';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment