Join documents by field that's not primary key

SQL++
1

I have an external system that creates documents (orders) on my platform. The reference to my platform is maintained through a string code, which is not a primary key. So, I have the following entities:

{
    docType: "submission",
    code: "XPTO28",
    name: "test-sub"
}

{
    docType: "order",
    code: "XPTO28",
    value: "100$"
}

Is there any query to associate order documents to submission documents without using primary keys, or must I do so programatically? Thank you in advance

2

Try this one:

SELECT object_remove(b,"code").*
  FROM (
                SELECT a.code,ARRAY_AGG({"name":a.name,"value":a.`value`}) AS result
                  FROM default a
                 GROUP BY a.code
  ) b;
3

Hello @atom_yang. I am not sure what you were trying to achieve, but this does not fetch the information about the first document; in fact, the whole query could be done without it. Am I missing something in your rationale?

4

The N1QL return the following data:

[
  {
    "result": [
      {
        "name": "test-sub"
      },
      {
        "value": "100$"
      }
    ]
  }
]
5

When I say sub documents, I mean the type of the document :slight_smile: I could’ve explained better. Let me edit my post accordingly.

I would expect to see some kind of join and a filter by docType (e.g WHERE a.docType='Submission'), since other documents might have the field code.