CODEWITHSUNDEEP
jsonpostgresql
Chandrashekhar BC
Chandrashekhar BC

Reputation: 29

Retrieve data from JSON object

Have problem is selecting local_desc based on the langtype which is passed thru a procedure .

Sample table and data for your reference 

create table test (local_id numeric, local_name json);

insert into test values (1, '{"language": [{"langtype": "EN", "Lang_desc": "English Smithline group"},{"langtype": "TH", "Lang_desc": "Thai Smithline group"}, {"langtype" :"AR", "Lang_desc":"Arabic Smithline group"}]}')

select local_id,local_name from test -- works fine

select local_id,local_name->'language' as lang from test    --- works fine

select local_id,local_name->'language'->>'Lang_desc' as lang from test where local_name->'language'->>'langtype'='EN' ---This query does not retrieve any data.. I want to get lang_desc based on the language parameter..

i am new to JSON and postgres. Pl help.

Upvotes: 3

Views: 70

Answers (1)

klin
klin

Reputation: 121474

local_name->'language' points to a json array. You should unpack the array using json_array_elements():

select local_id, elem
from test, json_array_elements(local_name->'language') elem;

 local_id |                            elem                            
----------+------------------------------------------------------------
        1 | {"langtype": "EN", "Lang_desc": "English Smithline group"}
        1 | {"langtype": "TH", "Lang_desc": "Thai Smithline group"}
        1 | {"langtype" :"AR", "Lang_desc":"Arabic Smithline group"}
(3 rows)

Use the above query to select filtered data:

select local_id, elem->>'Lang_desc' lang_desc
from (
    select local_id, elem
    from test, json_array_elements(local_name->'language') elem
    ) sub
where elem->>'langtype' = 'EN';

 local_id |        lang_desc        
----------+-------------------------
        1 | English Smithline group
(1 row)

Upvotes: 2

Related Questions