CODEWITHSUNDEEP
sqljsonpostgresql
user1428798
user1428798

Reputation: 1546

Query Json field on postgresql

I have a json column on my database pgsql enter image description here

I need to search keys with localId, I tried both this query:

SELECT *
FROM public.translations
    where datas->>'localId' = 7;

enter image description here and

 SELECT *
    FROM public.translations
        where datas->>'localId'::text = '7';

no results.

How can i do it please?

when i make this query i have no values

SELECT datas->>'localId' as local
FROM public.translations 
SELECT datas::json->>'localId' as local
FROM public.translations

enter image description here

Upvotes: 3

Views: 9984

Answers (3)

user330315
user330315

Reputation:

Your screenshot is a bit hard to read, but it seems your JSON is in fact a JSON array, so you need to pick the first element from there:

where (datas -> 0 ->> 'localId')::int = 7

or a bit shorter:

where (datas #>> '{0,localId}')::int = 7

alternatively you can use the contains operator @> to check if there is at least one element with localId = 7. But the @> operator requires jsonb, not json, so you will need to cast your column

where datas::jsonb @> '[{"localId": 7}]'

Online example

Upvotes: 7

Nguyễn Văn Quyền
Nguyễn Văn Quyền

Reputation: 350

What you need is ::json right after datas :

SELECT *
FROM public.translations
where datas::json->>'localId' = '7';

Related question on StackOverFlow

If you need more informations here is the Newest Postgresql Documentation

Upvotes: 1

Alan Millirud
Alan Millirud

Reputation: 1192

you need to cast both values to the same type

where datas->>'localId' = '7'::text;

or

where (datas->>'localId')::integer = 7;

or you should add brackets to you example

where (datas->>'localId')::text = '7';

Upvotes: 0

Related Questions