根据属性值选择单个 JSON 元素

huangapple go评论90阅读模式
英文:

select a single json element based on a property value

问题

  1. SELECT
  2. (jsonb_array_elements(tbl.jsonb_column) ->> 'Value') AS result
  3. FROM tbl
  4. WHERE (jsonb_array_elements(tbl.jsonb_column) ->> 'PropertyTypeId')::int = 2;
英文:

I have the following content in a jsonb column:

  1. [
  2. {
  3. "Value": "ABC",
  4. "PropertyTypeId": 1
  5. },
  6. {
  7. "Value": "CDE",
  8. "PropertyTypeId": 2
  9. },
  10. {
  11. "Value": "FGE",
  12. "PropertyTypeId": 3
  13. }
  14. ]

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:

  1. SELECT
  2. jsonb_array_elements(tbl.jsonb_column)@>'{"PropertyTypeId": 2}'
  3. 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

  1. 可以使用 JSON 路径查询:
  2. select jsonb_path_query_first(other_properties, '$[*] ? (@.PropertyTypeId == 2)')
  3. from the_table
英文:

You can use a JSON path query:

  1. select jsonb_path_query_first(other_properties, '$[*] ? (@.PropertyTypeId == 2)')
  2. from the_table

huangapple
  • 本文由 发表于 2023年3月9日 22:11:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75685760.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定