英文:
select a single json element based on a property value
问题
SELECT
(jsonb_array_elements(tbl.jsonb_column) ->> 'Value') AS result
FROM tbl
WHERE (jsonb_array_elements(tbl.jsonb_column) ->> 'PropertyTypeId')::int = 2;
英文:
I have the following content in a jsonb column:
[
{
"Value": "ABC",
"PropertyTypeId": 1
},
{
"Value": "CDE",
"PropertyTypeId": 2
},
{
"Value": "FGE",
"PropertyTypeId": 3
}
]
And I want to get the value of the element that has a property type of 2 for example.
I've gotten as closes as the following:
SELECT
jsonb_array_elements(tbl.jsonb_column)@>'{"PropertyTypeId": 2}'
FROM tbl
Above only tells me that weather the json contains "PropertyTypeId": 2 or not and it duplicates the rows to 3 rows, I only need one row with the "CDE" in the column.
答案1
得分: 0
可以使用 JSON 路径查询：
select jsonb_path_query_first(other_properties, '$[*] ? (@.PropertyTypeId == 2)')
from the_table
英文:
You can use a JSON path query:
select jsonb_path_query_first(other_properties, '$[*] ? (@.PropertyTypeId == 2)')
from the_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论