Retrieve details of reference from a list

SQL++
1

Hi,
Can you help me to construct a N1QL query to fetch all countries details of all references from the structure below

[

{
“name”: “1”,
“code”: “1”,
“countries”: [

    {
      "_type": "Country",
      "id": "212"
    },
    {
      "_type": "Country",
      "id": "92"

    }
]

},
{
“name”: “2”,
“code”: “2”,
“countries”: [

    {
      "_type": "Country",
      "id": "100"
    }
]

},

]

thanks

2

can you give an example result data that you want?

3

Hi I want such results. the id will be used to get details of the country.

[
{
1,1,[{code:212, name : france}, {code:92, name : United States}]
},
{
2,2,[{code:100, name : Singapore}]
}
]
Thank you

4

where is this data come from?

5

Actually I have 2 kinds of documents currency and country.

Currency is described as below and references many countries.
{
“name”: “2”,
“code”: “2”,
“countries”: [

{
  "_type": "Country",
  "id": "100"
}

]
}

id 100 referes to Country 100; which is located in another document and have the following structure
{
code: 100,
name:“Singapore”
}

6

FYI
sample document

INSERT INTO default VALUES("1", {"name": "1","code": "1","countries": [{"_type": "Country","id": "212"},{"_type": "Country","id": "92"}]});
INSERT INTO default VALUES("2", {"name": "2","code": "2","countries": [{"_type": "Country","id": "100"}]});
INSERT INTO default VALUES("100", {"code": 100,"name":"Singapore"});
INSERT INTO default VALUES("212", {"code": 212,"name":"france"});
INSERT INTO default VALUES("92", {"code": 92,"name":"United States"});

The N1QL

SELECT a.code,a.name,
       ARRAY_AGG({
                   "code":o.id,
                   "name":c.name
       }) AS countries
  FROM default a UNNEST a.countries o
  JOIN default c ON KEYS (o.id) 
 WHERE a.countries IS NOT MISSING
 GROUP BY a.code,a.name

The result

[
  {
    "code": "1",
    "countries": [
      {
        "code": "212",
        "name": "france"
      },
      {
        "code": "92",
        "name": "United States"
      }
    ],
    "name": "1"
  },
  {
    "code": "2",
    "countries": [
      {
        "code": "100",
        "name": "Singapore"
      }
    ],
    "name": "2"
  }
]