Hi i am trying to get data like this from query. How can i get data like this?

SQL++
,
1

Now, I am getting data like this.

{
“data”: {
“name”: “entertainment”,
“carriers”: [111,222,333]
}
}

but i want new entry/record if carriers are more than one like this

{
“data”: {
“name”: “entertainment”,
“carriers”: 111
}
},
{
“data”: {
“name”: “entertainment”,
“carriers”: 222
}
},
{
“data”: {
“name”: “entertainment”,
“carriers”: 333
}
}

How can i achieve this?

2

try this

SELECT {"name":d.`data`.name,
        "carriers":ARRAY_AGG(d.`data`.carriers)
        } AS `data` 
  FROM default as d
 GROUP BY d.`data`.name
3 
SELECT {"name":d.`data`.name,"carriers": c } AS `data` 
FROM default AS d UNNEST d.data.carriers AS c;

SELECT d.name, c AS carriers FROM default AS d UNNEST d.carriers AS c;
1 Like