Query Json field on postgresql

Question

I have a json column on my database pgsql

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

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

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 

Answer 1

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