Postgres statement for JSON_VALUE

Question

what is the postgres statement for this SQL statement.

SELECT * FROM table1 where JSON_VALUE(colB,'$.Items[0].Item') ='abc'

i have tried follow postgres document but result No function matches the given name and argument types

Answer 1

You can use the -> operator to access an element in an index.

SELECT * 
FROM table1 
where colb -> 'Items' -> 0 ->> 'Item' = 'abc'

colb -> 'Items' -> 0 returns the first array element of Items as a JSON value. And ->> 'Item' then returns the key "Item" from within that JSON as a text (aka varchar) value.

This requires that colb is defined as jsonb (or at least json). If not, you need to cast it like this colb::jsonb.
But in the long run you should really convert that column to jsonb then.

---

If you want to search for Item = 'abc' anywhere in the Items array (not just position 0), you can use the @> operator:

select *
from data
where colb @> '{"Items": [{"Item": "abc"}]}';

Online example: https://rextester.com/BQWB24156

The above can use a GIN index on the column colb. The first query will require an index on that expression.

---

With Postgres 12 you can use a JSON path query like you have:

SELECT * 
FROM table1 
where jsonb_path_exists(colb, '$.Items[0].Item' ? (@ == "abc")');

If you want to search anywhere in the array, you can use:

SELECT * 
FROM table1 
where jsonb_path_exists(colb, '$.Items[*].Item' ? (@ == "abc")');

That again can not make use of a GIN index on the column, it would require an index on that expression