Postgresql select json array into rows and single text

user_8091329 0
Viewed 6064

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

object postgresql arrays json
1 Answers

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

user_56349220
Related