如何使用PRESTO中的Where条件检索JSON数据

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

我想在这个查询中使用一个条件哪里的"org"="legal"我该如何实现？

Answer 1

在其中使用json_extract_scalar：

-- 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';

输出：

用户
Bob Smith