Extract JSON To Multiple Records using N1QL

SQL++
,
1

Hello,

I need help in extracting json object to multiple records using N1QL Query. See below example for better explaination.

I have response like below :
[
{
“carriers”: [
92,
93,
94
],
“countryName”: “China”,
“countryId”: 46,
}
]

Here, carriers array contains the ID of Carriers, I want to extract all those IDs in multiple records. I want output as below :

[
{
“carrierID” : 92,
“carrierName”:“China Mobile”
“countryName”: “China”,
“countryId”: 46,
},
{
“carrierID” : 93,
“carrierName”:“China Telecom”
“countryName”: “China”,
“countryId”: 46,
},
{
“carrierID” : 94,
“carrierName”:“China Unicom”
“countryName”: “China”,
“countryId”: 46,
}
]

The query I tried as below :

SELECT
cntry.name countryName,
cntry._id countryId,
cntry.carriers._id carrierId,
cntry.carriers.name carrierName,
FROM default as cntry
where cntry._type=‘Country’ AND cntry._id=46 AND ANY x IN cntry.carriers SATISFIES x._id IN [92,93,94] END ;

====================================
The Document for _type=‘Country’ with the name ‘China’ and _id 46 is as below :
[
{
“default”: {
"_id": 46,
"_type": “Country”,
“carriers”: [
{
"_id": 92,
“name”: “China Mobile”,
“status”: 1
},
{
"_id": 93,
“name”: “China Telecom”,
“status”: 1
},
{
"_id": 94,
“name”: “China Unicom”,
“status”: 1
},
{
"_id": 95,
“name”: “Wifi or not identified”,
“status”: 1
}
],
“currency”: “Yuan”,
“currencyCode”: “CNY”,
“dialingPrefix”: “86”,
“iso2”: “CN”,
“iso3”: “CHN”,
“latitude”: 35.86166,
“longitude”: 104.195397,
“name”: “China”,
}
}
]

Any help appreciated

2

you can use `` UNNEST ```, for example

SELECT cntry.name countryName,
       cntry._id countryId,
       c._id carrierId,
       c.name carrierName
  FROM default as cntry UNNEST cntry.carriers c
 WHERE cntry._type='Country' 
   AND cntry._id=46
   AND c._id IN [92,93,94]
1 Like