4

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

2

2 Answers 2

3

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

Sign up to request clarification or add additional context in comments.

Comments

1

Something like this.

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

DEMO

4 Comments

ERROR: operator does not exist: text -> unknown LINE 3: cross join json_array_elements(colb->'Items') as j ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
i saw your demo, i do not need to further process that column. just simple like sql query, display the row record.
@user12158726 : what's the datatype of colB? It should be json, otherwise you need to cast it.
the datatype is nvarchar , json strings stored there

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.