'postgresql using json sub-element

How do I select only "A" named values from a postgresql database table.

Id Column
1001 {"results":[{"name":"A","value":"7.8"}, {"name":"B","value":"0.5"}]}
1002 {"results":[{"name":"B","value":"5.4"}, {"name":"D","value":"4.5"}]}
1003 {"results":[{"name":"D","value":"4.8"}, {"name":"A","value":"6.7"}]}

Results should be as

ID Name Value
1001 A 7.8
1003 A 6.7
postgresql


Solution 1:[1]

You can use a JSON path query to access such an element:

select id, 
       'A' as name
       jsonb_path_query_first("column", '$.results[*] ? (@.name == "A").value') #>> '{}' as value
from the_table;

This assumes that column (which is a horrible name) is defined as jsonb (which it should be). If it's not, you need to cast it "column"::jsonb

jsonb_path_query_first returns a jsonb value and there is no straighforward way to convert that to a proper text value (as e.g. ->> does). The #>> '{}' is a little hack to convert a scalar jsonb value to text.

Solution 2:[2]

According to column type you can use json_to_recordset (If type of column is json) or jsonb_to_recordset (If type of column is jsonb)

Demo

  • JSONB sample
select
  t.id,
  x.name,
  x.value
from
  test t
  cross join jsonb_to_recordset(("column"::jsonb) -> 'results') as x(name text, value text)
where
  x.name = 'A'
  • JSON sample
select
  t.id,
  x.name,
  x.value
from
  test t
  cross join json_to_recordset(("column"::json) -> 'results') as x(name text, value text)
where
  x.name = 'A'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name
Solution 2

Related Questions