Search code examples
sqljsonpostgresqljsonb

select a single json element based on a property value

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.

Solution

  • You can use a JSON path query:

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