CODEWITHSUNDEEP
sqljsonpostgresql
user2201789
user2201789

Reputation: 1211

Postgres statement for JSON_VALUE

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

Upvotes: 4

Views: 9311

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 3

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

Something like this.

SELECT t.*
     FROM table1  t
    cross join json_array_elements(colb->'Items') as j
where j->>'Item' = 'abc'

DEMO

Upvotes: 1

Related Questions