英文:
Select rows where a value is found in json array
问题
以下是翻译好的部分：
我有一个名为 uploads_table 的表格：
| upload_id | value |
|---|---|
| A | [{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}] |
| B | [{"doc_name": "doc2a", "doc_type": "csv"}, {"doc_name": "doc2b", "doc_type": "csv"}] |
| C | [{"doc_name": "doc3a", "doc_type": "pdf"}] |
要返回所有在 uploads_table 中至少有一个 "doc_type" 为 "pdf" 的 upload_id 和 value，您可以使用以下的Postgres查询：
SELECT upload_id, value
FROM uploads_table
WHERE jsonb_array_elements(value)->>'doc_type' = 'pdf';
期望的结果：
| upload_id | value |
|---|---|
| A | [{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}] |
| C | [{"doc_name": "doc3a", "doc_type": "pdf"}] |
英文:
I have a table uploads_table:
| upload_id | value |
|---|---|
| A | [{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}] |
| B | [{"doc_name": "doc2a", "doc_type": "csv"}, {"doc_name": "doc2b", "doc_type": "csv"}] |
| C | [{"doc_name": "doc3a", "doc_type": "pdf"}] |
What would be the Postgres query to return all the upload_id, value from uploads_table which has at least one "doc_type" as "pdf".
Expected result:
| upload_id | value |
|---|---|
| A | [{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}] |
| C | [{"doc_name": "doc3a", "doc_type": "pdf"}] |
答案1
得分: 4
SELECT *
FROM uploads_table
WHERE value @> jsonb '[{"doc_type":"pdf"}]';
当然，这是假设 value 是 jsonb 类型 - 正如它应该是的。
在 (value) 上创建一个 GIN 索引将使查询快速。使用更专业的 jsonb_path_ops 索引会更快:
CREATE INDEX uploads_table_values_gin_idx ON uploads_table USING gin (value jsonb_path_ops);
参考:
相关:
- https://stackoverflow.com/questions/73969448/query-for-key-in-an-element-of-a-json-array/74009722#74009722
- https://stackoverflow.com/questions/74503708/select-by-json-array-value/74504773#74504773
英文:
Use the jsonb "contains" operator @>:
SELECT *
FROM uploads_table
WHERE value @> jsonb '[{"doc_type":"pdf"}]';
This is, of course, assuming that value is type jsonb - as it should be.
A GIN index on (value) will make this fast.
Even faster with a more specialized jsonb_path_ops index:
CREATE INDEX uploads_table_values_gin_idx ON uploads_table USING gin (value jsonb_path_ops);
See:
Related:
答案2
得分: 0
You can use the built-in function jsonb_array_elements in PostgreSQL. The query statement will be as follows:
SELECT *
FROM uploads_table
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(value) AS doc
WHERE doc->>'doc_type' = 'pdf'
);
The result is as shown in the image below:
英文:
You can use the built-in function jsonb_array_elements in PostgreSQL. The query statement will be as follows:
SELECT *
FROM uploads_table
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(value) AS doc
WHERE doc->>'doc_type' = 'pdf'
);
The result is as shown in the image below:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论