I need to use concatenation for two array elements for multiple entries.
[
{
“codes”: [
{
“cc”: “387”,
“ndc”: “603”,
“primary”: 1,
“timezone”: “Europe/Sarajevo”
},
{
“cc”: “387”,
“ndc”: “60400”,
“primary”: 0,
“timezone”: “Europe/Sarajevo”
}
]
}
]
select codes[0].cc||codes[0].ndc from config
this is working fine but prints just first elements together:-
[
{
“$1”: “387603”
}
]
I need to print both at a time, I have tried below query using * instead of 0 for the first occurrence but it didn’t worked!
select codes[asterix].cc||codes[asterix].ndc from config
Expected output:
[
{
“$1”: “387603”
“$2”: “38760400”
}
]
Kindly suggest the possible options to print all the concatenation in one go.
hi,
I think you case use the array_agg combine with unnest function like this :
select array_agg([c.cc,c.ndc]) from config unnest codes c
good luck
1 Like
Hey, thanks for your input.
I have tried below query but faced issue in execution!
Query:- select array_agg([code.cc,code.ndc]) from config
unnest codes AS code where networkid=24
O/P:-
{
“code”: 3000,
“msg”: “Ambiguous reference to field networkid.”,
“query_from_user”: “select array_agg([code.cc,code.ndc]) from config
unnest codes AS code where networkid=24”
}
Though there is a networkid for 24 exist in the config bucket!
you must prefix the last condition like this :
select array_agg([code.cc,code.ndc]) from config
c unnest codes AS code where c.networkid=24
Thanks a lot, it worked with little manipulation .
select code.cc||code.ndc as vpmn from config
c unnest codes as code where c.networkid=24
1 Like