Postgresql select json array into rows and single text

Question

I have query to get result from table like this:

SELECT  test_id, content::json->'scenario'
FROM    test

And i got these result, with array of objects in the scenario column:

test_id | scenario
29      | [{"name":"OpenSignal", "task":[{"name":"speedtest"}]}, {"name":"ITest", "task":[{"name":"speedtest"}]}, {"name":"EqualOne", "task":[{"name":"flashtest"}, {"name":"web"}, {"name":"video"}]}]
30      | [{"name":"Speedtest", "task":[{"name":"speedtest"}]}, {"name":"ITest", "task":[{"name":"speedtest"}]}, {"name":"EqualOne", "task":[{"name":"flashtest"}, {"name":"web"}, {"name":"video"}]}]

The object structure is like this:

[{
    "name": "OpenSignal",
    "task": [{
        "name": "speedtest"
    }]
}, {
    "name": "ITest",
    "task": [{
        "name": "speedtest"
    }]
}, {
    "name": "EqualOne",
    "task": [{
        "name": "flashtest"
    }, {
        "name": "web"
    }, {
        "name": "video"
    }]
}]

How can i get result like these:

test_id | scenario
29      | Opensignal-speedtest
29      | ITest-speedtest
29      | EqualOne-flashtest
29      | EqualOne-web
29      | EqualOne-video
30      | Opensignal-speedtest
30      | ITest-speedtest
30      | EqualOne-flashtest
30      | EqualOne-web
30      | EqualOne-video

And

test_id | scenarios
29      | OpenSignal-speedtest,ITest-speedtest,EqualOne-flashtest, EqualOne-web,EqualOne-video
30      | Speedtest-speedtest,ITest-speedtest,EqualOne-flashtest,EqualOne-web,EqualOne-video

Thanks in advance my brothers

Its always nice to see what yo have tried. It helps us help you. postgresql.org/docs/current/static/functions-json.html — Gurmokh
– Gurmokh, Commented Nov 10, 2017 at 10:53
@Mokadillion thanks bro. i have learned that i need to show my script to get maximum help and support from the bros here — Rakaziwi
– Rakaziwi, Commented Nov 10, 2017 at 15:25

Dries De Rydt · Accepted Answer · 2017-11-10 12:31:20Z

6

For your first query, you could do something like this:

SELECT test_id, CONCAT(sub.element->'name', '-', json_array_elements(sub.element->'task')->'name') as scenario
FROM 
  (SELECT test_id, json_array_elements(content::json) as element 
   FROM test) as sub;

I used a subquery to get the elements from your original json, and then I concatenate the name with each task name with a dash.

Then, to easily get them separated per id, I wrapped it in another subquery using the string_agg function:

SELECT test_id, 
       string_agg(task, ',')
FROM(
    SELECT test_id, CONCAT(sub.element->'name', '-', json_array_elements(sub.element->'task')->'name') as task
    FROM 
      (SELECT test_id, json_array_elements(content::json) as element 
       FROM test) as sub
)as tasks 
 GROUP BY test_id

Sorry if it looks a bit messy, here is an sqlfiddle link you can use. http://sqlfiddle.com/#!17/fcb27/38

answered Nov 10, 2017 at 12:31

Dries De Rydt

8085 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rakaziwi Over a year ago

umm sorry, but there is an error occured with the error message like this: "ERROR: cannot call json_array_elements on a non-array". i think the error is because my scenario value is not an array type. your sqlfiddle is working because you set the scenario value as text (you add single qoute in the array) and mine dont have the single qoute. how can i cast the scenario into an array?

Dries De Rydt Over a year ago

you see the type of my content column is json. Adding json is with quotes, like here: postgresqltutorial.com/postgresql-json I think maybe I misunderstood your data format, though if your scenarios are objects instead of arrays, it should be even easier to process them. Perhaps you can modify the fiddle so that the data has the right format?

Collectives™ on Stack Overflow

Postgresql select json array into rows and single text

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related