CODEWITHSUNDEEP
postgresql
Pranav
Pranav

Reputation: 143

Search data from json value in sql

We have following data in details column for one of the table:

{"_translated": {"en_GB": {"name": "Bathroom 2", "description": "Bathroom 2"}, "en_US": {"name": "Bathroom 2", "description": "Bathroom 2"}, "es_ES": {"name": "Baño 2", "description": "Baño 2"}, "es_MX": {"name": "Baño 2", "description": "Baño 2"}, "fr_FR": {"name": "Salle de bain 2", "description": "Salle de bain 2"}, "hi_IN": {"name": "स्नानघर 2", "description": "स्नानघर 2"}, "zh_CN": {"name": "浴室 2", "description": "浴室 2"}, "en_GB.machine": {"name": "Bathroom 2", "description": "Bathroom 2"}, "es_MX.machine": {"name": "Baño 2", "description": "Baño 2"}, "field_locales": {"name": "en_US", "description": "en_US"}, "hi_IN.machine": {"name": "स्नानघर 2", "description": "स्नानघर 2"}, "zh_CN.machine": {"name": "浴室2", "description": "浴室2"}, "default_locale": "en_US"}}

{"_translated": {"en_GB": {"name": "Office", "description": "Office"}, "en_US": {"name": "Office", "description": "Office"}, "es_ES": {"name": "Oficina", "description": "Oficina"}, "es_MX": {"name": "Oficina", "description": "Oficina"}, "fr_FR": {"name": "Bureau", "description": "Bureau"}, "hi_IN": {"name": "कार्यालय", "description": "कार्यालय"}, "zh_CN": {"name": "办公室", "description": "办公室"}, "es_MX.machine": {"name": "Oficina", "description": "Oficina"}, "field_locales": {"name": "en_US", "description": "en_US"}, "hi_IN.machine": {"name": "कार्यालय", "description": "कार्यालय"}, "zh_CN.machine": {"name": "办公室", "description": "办公室"}, "default_locale": "en_US"}}

{"_translated": {"en_GB": {"name": "Bedroom 2", "description": "Bedroom 2"}, "en_US": {"name": "Bedroom 2", "description": "Bedroom 2"}, "es_ES": {"name": "Habitación 2", "description": "Habitación 2"}, "es_MX": {"name": "Habitación 2", "description": "Habitación 2"}, "fr_FR": {"name": "Chambre 2", "description": "Chambre 2"}, "hi_IN": {"name": "शयनकक्ष 2", "description": "शयनकक्ष 2"}, "zh_CN": {"name": "卧室 2", "description": "卧室 2"}, "es_MX.machine": {"name": "Dormitorio 2", "description": "Dormitorio 2"}, "field_locales": {"name": "en_US", "description": "en_US"}, "hi_IN.machine": {"name": "शयनकक्ष 2", "description": "शयनकक्ष 2"}, "zh_CN.machine": {"name": "卧室2", "description": "卧室2"}, "default_locale": "es_ES"}}

Now I want to find all those records having "default_locale": "en_US" and those records for which its not present in translated.

SELECT details FROM locations where details->'default_locale' = 'es_ES';

But this not works. Any help appreciated.

Upvotes: 0

Views: 42

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13069

SELECT details
 FROM locations
WHERE details -> '_translated' ->> 'default_locale' = 'en_US' 
   OR details -> '_translated' ->> 'default_locale' is null;

Not related, but as @stickybit pointed above - review your data design. Having to search/filter by JSON attribute values is not a good symptom at all.

Upvotes: 1

Related Questions