How to get specific element of json array without using "WHERE" clause?

The database has a column with json objects in the form:

// row 1
[
  {'id': 1, 'data': 'foo'},
  {'id': 2, 'data': 'bar'},
  {'id': 3, 'data': 'baz'}
]

// row 2
[
  {'id': 1, 'data': 'fus'},
  {'id': 2, 'data': 'ro'},
  {'id': 3, 'data': 'dah'}
]

The task is to get an array with a certain id from each line - for example with id = 2:

// row 1
{'id': 2, 'data': 'bar'}

// row 2
{'id': 2, 'data': 'ro'}

I was able to make a request using "CASE":

SELECT
  CASE when (t.data::json->0->'id')::varchar::int = 2 then (t.data::json->0)::varchar
       when (t.data::json->1->'id')::varchar::int = 2 then (t.data::json->1)::varchar
       when (t.data::json->2->'id')::varchar::int = 2 then (t.data::json->2)::varchar
       else null::varchar
  end as "result"
FROM db.info as t;

That is, using indexes, I go inside each array to get the value by key. If the key matches, then I get an array by its index. But the problem will arise if there are 4 arrays inside the object.

It is possible to write a sql query without using "WHERE" or "HAVING clause - only inside "SELECT" without relying on indices?

Solution

I'd assume that your data is in jsonb format, cause there's almost no reason not to use it if your version of Postgres supports it:

The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

In this case you can use jsonpath for that. You can either embed parameter (id == 2) into path:

select
    jsonb_path_query(t.data, '$[*] ? (@.id == 2)')
from db.info as t

or parametrize it:

select
    jsonb_path_query(
        t.data,
        '$[*] ? (@.id == $id)',
        jsonb_build_object('id', 2)::jsonb
    )
from db.info as t


{"id": 2, "data": "bar"}
{"id": 2, "data": "ro"}

Alternatively, you can use jsonb_array_elements / json_array_elements:

select
    (select tt.value from jsonb_array_elements(t.data) as tt where tt->>'id' = '2')
from db.info as t


{"id": 2, "data": "bar"}
{"id": 2, "data": "ro"}