英文:
How to query objects in jsonb with specific value criteria
问题
I'm trying to query a jsonb column in Postgres. I've created it from a nested java map, it isn't in array format, and can't work out if that is the thing that is causing me to have issues with my query.
我正在尝试查询Postgres中的jsonb列。我将其从嵌套的Java映射创建，它不是数组格式，并且无法确定是否是导致我的查询出现问题的原因。
I want to query the jsonb column for items containing an object with "type": "Unknown" and also have a time that is greater than 100.
我想查询jsonb列中包含具有"type": "Unknown"并且时间大于100的项目。
id|uid |process_stat_json |
---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1|9cf237e8-0c73-4c4d-a60c-30b244789e67|{"Type A": {"time": 500, "complete": 100.0, "type": "Unknown"}, "Type B": {"time": 35, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 50, "complete": 100.0, "type": "Serial"}}
2|07ac957f-c9e4-460e-b75f-79c0d9c550d4|{"Type A": {"time": 55, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 20, "complete": 100.0, "type": "Serial"}}
3|8e8ef827-fff1-4ea1-a990-206b557ef27a|{"Type C": {"time": 100, "complete": 100.0, "type": "Parallel"}, "Type A": {"time": 55, "complete": 100.0, "type": "Unknown"}, "Type D": {"time": 70, "complete": 100.0, "type": "Serial"}}
4|5279e6ae-f2e8-4fae-8d0b-9297b5e2936a|{"Type A": {"time": 200, "complete": 100.0, "type": "Serial"}, "Type D": {"time": 20, "complete": 100.0, "type": "Unknown"}, "Type C": {"time": 80, "complete": 100.0, "type": "Serial"}}
So for example the query would return id:1
因此，例如查询将返回id:1
I've started creating a query to search for items with the type "Unknown"
我已经开始创建一个查询来搜索类型为"Unknown"的项目
select p.*
from
process p
where
p.process_stat_json @> '{"type":"Unknown"}';
But this isn't even returning any items at all, let alone allowing me to query them, so unsure how to proceed from here.
但这甚至没有返回任何项目，更不用说允许我查询它们了，所以不确定该如何继续。
英文:
I'm trying to query a jsonb column in Postgres. I've created it from a nested java map, it isn't in array format, and can't work out if that is the thing that is causing me to have issues with my query.
I want to query the jsonb column for items containing an object with '"type": "Unknown" and also have a time that is greater than 100.
id|uid |process_stat_json |
---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1|9cf237e8-0c73-4c4d-a60c-30b244789e67|{"Type A": {"time": 500, "complete": 100.0, "type": "Unknown"}, "Type B": {"time": 35, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 50, "complete": 100.0, "type": "Serial"}}
2|07ac957f-c9e4-460e-b75f-79c0d9c550d4|{"Type A": {"time": 55, "complete": 100.0, "type": "Parallel"}, "Type C": {"time": 20, "complete": 100.0, "type": "Serial"}}
3|8e8ef827-fff1-4ea1-a990-206b557ef27a|{"Type C": {"time": 100, "complete": 100.0, "type": "Parallel"}, "Type A": {"time": 55, "complete": 100.0, "type": "Unknown"}, "Type D": {"time": 70, "complete": 100.0, "type": "Serial"}}
4|5279e6ae-f2e8-4fae-8d0b-9297b5e2936a|{"Type A": {"time": 200, "complete": 100.0, "type": "Serial"}, "Type D": {"time": 20, "complete": 100.0, "type": "Unknown"}, "Type C": {"time": 80, "complete": 100.0, "type": "Serial"}}
So for example the query would return id:1
I've started creating a query to search for items with the type "Unknown"
select p.*
from
process p
where
p.process_stat_json @> '{"type":"Unknown"}';
But this isn't even returning any items at all, let alone allowing me to query them, so unsure how to proceed from here.
答案1
得分: 1
Your first request can be solved elegantly with the JSON path operator @> - in Postgres 12 or later:
SELECT p.*
FROM process p
WHERE p.process_stat_json @? ''$.* ? (@.type == "Unknown") ? (@.time >= 100)'';
Related:
英文:
Your first request can be solved elegantly with the JSON path operator @> - in Postgres 12 or later:
SELECT p.*
FROM process p
WHERE p.process_stat_json @? '$.* ? (@.type == "Unknown") ? (@.time >= 100)';
Related:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论