Join with array N1QL

SQL++
,
1

Hi, I have the following json objects and I want to make a join with the top level and their childres.
I’ve achieved to make a join between the higher json object level and the another document, but I can’t make the same join with the sub-documents belonging higher json object level.

Document a:

[
  {
    "node": {
      "name": "products",
      "sections": [
        {
          "name": "refund",
          "sections": []
        }
      ]
    }
  },
  {
    "node": {
      "name": "info",
      "sections": [
        {
          "name": "contact",
          "sections": []
        },
        {
          "name": "help",
          "sections": []
        }
      ]
    }
  }
]

Document B:

[
  {
    "word": {
      "_id": "en_US-info",
      "wordKey": "info",
      "wordValue": "Information"
    }
  },
  {
    "word": {
      "_id": "en_US-products",
      "wordKey": "products",
      "wordValue": "Products"
    }
  },
  {
    "word": {
      "_id": "es_ES-info",
      "wordKey": "info",
      "wordValue": "Informacion"
    }
  },
  {
    "word": {
      "_id": "es_ES-products",
      "wordKey": "products",
      "wordValue": "Productos"
    }
  },
  {
    "word": {
      "_id": "es_ES-refund",
      "wordKey": "refund",
      "wordValue": "Reembolso"
    }
  },
  {
    "word": {
      "_id": "es_ES-help",
      "wordKey": "help",
      "wordValue": "Ayusa"
    }
  },
  {
    "word": {
      "_id": "en_US-refund",
      "wordKey": "refund",
      "wordValue": "Refund"
    }
  },
  {
    "word": {
      "_id": "en_US-help",
      "wordKey": "help",
      "wordValue": "Help"
    }
  },
  {
    "word": {
      "_id": "es_ES-contact",
      "wordKey": "contact",
      "wordValue": "Contacto"
    }
  },
  {
    "word": {
      "_id": "en_US-contact",
      "wordKey": "contact",
      "wordValue": "Contact"
    }
  }
]

Desired output:

[
    {
        "name": "Informacion",
        "sections": [
            {
                "name": "Contacto",
                "sections": []
            },
            {
                "name": "Ayuda",
                "sections": []
            }
        ]
    },
    {
        "name": "Productos",
        "sections": [
            {
                "name": "Reembolso",
                "sections": []
            }
        ]
    }
]

But, In sections array, the name is not resolved by the join.
This is the query I’ve developed:

SELECT node.*, word.wordValue as name FROMtestword JOINtestnode ON KEY "es_ES-" || node.name FOR word

The output of the query:

[
    {
        "name": "Informacion",
        "sections": [
            {
                "name": "contact",
                "sections": []
            },
            {
                "name": "help",
                "sections": []
            }
        ]
    },
    {
        "name": "Productos",
        "sections": [
            {
                "name": "refund",
                "sections": []
            }
        ]
    }
]
2

You need to repeat that each and every level. If it is repeated nested you need to repeat that.

SELECT ARRAY OBJECT_PUT(v,"name", (SELECT RAW w.wordValue FROM test w USE KEYS "es-ES-"|| v.name)[0])  FOR v IN node.sections END AS sections, word.wordValue as name
FROM test word 
JOIN test node 
ON KEY "es_ES-" || node.name FOR word;
3

Hi @vsr1, thanks for your response!

I have two doubts about your solution:

1 - In sections attribute, It can be more than one level, so, we need to make as joins as levels. Example:

[
    {
        "name": "Informacion",
        "sections": [
            {
                "name": "Contacto",
                "sections": []
            },
            {
                "name": "Ayuda",
                "sections": [
                     {
                         "name": "Email",
                         "sections": []
                     },
                     {
                         "name": "Teléfono",
                         "sections": []
                     }
                ]
            }
        ]
    },
    {
        "name": "Productos",
        "sections": [
            {
                "name": "Reembolso",
                "sections": []
            }
        ]
    }
]

2 - When I execute your query, in sections sub-nodes the name is omitted. If I have change ...USE KEYS "es-ES-"|| v.name)[0]) FOR v.... by ...USE KEYS "es-ES-"|| v.name)[*]) FOR v... the json output has an empty name attribute.

Could you help me, please?

4

I’ve been researching about joins and I’ve developed the following query:

SELECT node.*, word.wordValue as name , sections.* FROM test word JOIN test node ON KEY "es_ES-" || node.name FOR word JOIN test sections ON KEYS ARRAY "es_ES-" || subsection.name FOR subsection IN node.sections END

the output is better than the previous but it is not the desired output :frowning:


[
  {
    "name": "Informacion",
    "sections": [
      {
        "name": "contact",
        "sections": []
      },
      {
        "name": "help",
        "sections": []
      }
    ],
    "wordKey": "contact",
    "wordValue": "Contacto"
  },
  {
    "name": "Informacion",
    "sections": [
      {
        "name": "contact",
        "sections": []
      },
      {
        "name": "help",
        "sections": []
      }
    ],
    "wordKey": "help",
    "wordValue": "Ayuda"
  },
  {
    "name": "Productos",
    "sections": [
      {
        "name": "subProduct1",
        "sections": []
      },
      {
        "name": "subProduct2",
        "sections": []
      }
    ],
    "wordKey": "subProduct1",
    "wordValue": "Sub-Producto"
  },
  {
    "name": "Productos",
    "sections": [
      {
        "name": "subProduct1",
        "sections": []
      },
      {
        "name": "subProduct2",
        "sections": []
      }
    ],
    "wordKey": "subProduct2",
    "wordValue": "Sub-Producto 2"
  }
]
5

If there is recursion you need to take care of it.