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
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}]'
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
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';
