Concatenation of two array elements using N1QL for n number of subsequent array elements

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