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

Results should be as

CodePudding user response：

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'

CodePudding user response：

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.