How Retrieve JSON DATA using Where Condition In PRESTO?

Question

WITH dataset AS (SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal"}'),
    (JSON '{"name": "Susan Smith", "org": "engineering"}'),
    (JSON '{"name": "Jane Smith", "org": "finance"}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset

I want use a condition in this query Like Where 'org' = 'legal' How can i achieve it?

Answer 1

Use json_extract_scalar in the where:

-- sample data
WITH dataset(users) AS (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal"}'),
    (JSON '{"name": "Susan Smith", "org": "engineering"}'),
    (JSON '{"name": "Jane Smith", "org": "finance"}')
)

-- query
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_extract_scalar(users, '$.org') = 'legal';

Output:

user
Bob Smith