Skip to content

Instantly share code, notes, and snippets.

@velotiotech

velotiotech/Searching for Values in JSON

Last active July 3, 2020 15:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save velotiotech/c1df392f6f1acf4c5f4856fdb51958ad to your computer and use it in GitHub Desktop.
Save velotiotech/c1df392f6f1acf4c5f4856fdb51958ad to your computer and use it in GitHub Desktop.
Download ZIP
Searching for Values in JSON
Raw
Searching for Values in JSON
WITH dataset AS (
SELECT * FROM (VALUES
(JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
(JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
(JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment