Skip to content

Instantly share code, notes, and snippets.

@dillonstreator

dillonstreator/postgres-jsonb-path-query.sql

Created November 17, 2021 17:31
Show Gist options
  • Save dillonstreator/85672df13cedd00c5cf3066191d32e3a to your computer and use it in GitHub Desktop.
Save dillonstreator/85672df13cedd00c5cf3066191d32e3a to your computer and use it in GitHub Desktop.
Download ZIP
postgres jsonb path query retrieve field in object with field that is array of objects
Raw
postgres-jsonb-path-query.sql
SELECT
id,
jsonb_path_query(json_field::jsonb, '$.arrayfield[*] ? (@.fieldid == $fieldid)', '{"fieldid": "field-id-value-to-find"}')->>'fieldvalue' as field_value
FROM table
WHERE json_field::jsonb->'arrayfield' @> '[{"fieldid": "field-id-value-to-find"}]';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment