1

I have JSON data as follows:

"group_nw4qu40":[
{"group_nw4qu40/Special_Characteristics":"11"},
{"group_nw4qu40/Special_Characteristics":"0"},
{"group_nw4qu40/Special_Characteristics":"0"},
{"group_nw4qu40/Special_Characteristics":"1"},
{"group_nw4qu40/Special_Characteristics":"1"}]

My task is to Traverse though the Array within an array to fetch the "Special_Characteristic" of each individual member and create a table having these values in Multiple Rows under same column.

I tried using this query but, it fetches me values in multiple columns which is incorrect for me.

select id AS id, (json->'group_nw4qu40'->>0)::json->>'group_nw4qu40/Special_Characteristics' AS value_of_special_characteristic from public.logger_instance where id = 5215

Please help me get these values in multiple Rows under same column.

1 Answer 1

1

Use jsonb_array_elements() or json_array_elements(), e.g.:

with the_data (json) as (
    values (
        '{"group_nw4qu40":[
        {"group_nw4qu40/Special_Characteristics":"11"},
        {"group_nw4qu40/Special_Characteristics":"0"},
        {"group_nw4qu40/Special_Characteristics":"0"},
        {"group_nw4qu40/Special_Characteristics":"1"},
        {"group_nw4qu40/Special_Characteristics":"1"}]}'::jsonb)
    )
select e->>'group_nw4qu40/Special_Characteristics' value_of_special_characteristic
from the_data, jsonb_array_elements(json->'group_nw4qu40') e

 value_of_special_characteristic 
---------------------------------
 11
 0
 0
 1
 1
(5 rows)

Your actual query should look like this:

select id, e->>'group_nw4qu40/Special_Characteristics' value_of_special_characteristic
from public.logger_instance, jsonb_array_elements(json->'group_nw4qu40') e 
where id = 5215;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.