N1QL Query to join array fields with an array in another document

SQL++
1

I have 3 documents types :

Data

    {
      "formId": "7508e7b2-bcf7-437b-a206-9fee87256d01",
      "dataValues": [
        {
          "questionId": "Someguid123",
          "questionValue": "Question1"
        },
        {
          "questionId": "Someguid",
          "questionValue": "Question2"
        },
        {
          "questionId": "AnotherGuid",
          "questionValue": "Question3"
        }
      ],
      "lastUpdateDateTime": "2023-01-04T10:56:49Z",
      "type": "Data",
      "templateId": "41e4cc2c-e9fb-4bdc-9dc2-af19e5988984",
      "creationDateTime": "2022-12-28T11:20:46Z"
    }

AttachedDocuments

    {
      "id": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "lastUpdateDateTime": "2023-01-05T11:47:17Z",
      "documents": [
        {
          "isUploaded": false,
          "id": "DocumentMetadata::001",
          "isDeleted": false,
          "type": "photo",
          "parentId": "Someguid123"
        },
        {
          "isUploaded": false,
          "id": "DocumentMetadata::002",
          "isDeleted": false,
          "type": "photo",
          "parentId": "Someguid123"
        }
      ],
      "type": "AttachedDocuments",
      "parentDocId": "MyFormData::7508e7b2-bcf7-437b-a206-9fee87256d01",
      "creationDateTime": "2022-12-28T11:20:46Z"
    }

DocumentMetaData

    {
      "id": "DocumentMetadata::001",
      "type": "DocumentMetadata",
      "name": "MyForm_001.png",
      "documentId": "549c4da2-ad3a-4f92-bfa2-019750a11007",
      "contentType": "FILE",
      "parentDocumentId": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "creationDateTime": "2023-01-04T10:56:49Z"
    },
    {
      "id": "DocumentMetadata::002",
      "type": "DocumentMetadata",
      "name": "MyForm_002.png",
      "documentId": "549c4da2-ad3a-4f92-bfa2-019750a11007",
      "contentType": "FILE",
      "parentDocumentId": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "creationDateTime": "2023-01-04T10:56:49Z"
    }

Every Data type document has only one AttachedDocuments document with parentDocId* field set to formId field of Data document.

If items in Data.dataValues has a document attached to it, AttachedDocuments.documents array have items with parentId field set to Data.dataValues[i].questionId.

Also every AttachedDocuments.documents[i] item has a DocumentMetadata document with id of AttachedDocuments.documents[i].id field.

I want to have a query which returns all Data.dataValues as an array but containing a field links that contains the DocumentMetadata.name field like below :

   [
    {
      "questionId": "Someguid123",
      "questionValue": "Question1",
      "links": ["MyForm_001.png", "MyForm_002.png"]
    },
    {
      "questionId": "Someguid",
      "questionValue": "Question2"
    },
    {
      "questionId": "AnotherGuid",
      "questionValue": "Question3"
    }
  ]

I have 3 documents types :

Data

    {
      "formId": "7508e7b2-bcf7-437b-a206-9fee87256d01",
      "dataValues": [
        {
          "questionId": "Someguid123",
          "questionValue": "Question1"
        },
        {
          "questionId": "Someguid",
          "questionValue": "Question2"
        },
        {
          "questionId": "AnotherGuid",
          "questionValue": "Question3"
        }
      ],
      "lastUpdateDateTime": "2023-01-04T10:56:49Z",
      "type": "Data",
      "templateId": "41e4cc2c-e9fb-4bdc-9dc2-af19e5988984",
      "creationDateTime": "2022-12-28T11:20:46Z"
    }

AttachedDocuments

    {
      "id": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "lastUpdateDateTime": "2023-01-05T11:47:17Z",
      "documents": [
        {
          "isUploaded": false,
          "id": "DocumentMetadata::001",
          "isDeleted": false,
          "type": "photo",
          "parentId": "Someguid123"
        },
        {
          "isUploaded": false,
          "id": "DocumentMetadata::002",
          "isDeleted": false,
          "type": "photo",
          "parentId": "Someguid123"
        }
      ],
      "type": "AttachedDocuments",
      "parentDocId": "MyFormData::7508e7b2-bcf7-437b-a206-9fee87256d01",
      "creationDateTime": "2022-12-28T11:20:46Z"
    }

DocumentMetaData

    {
      "id": "DocumentMetadata::001",
      "type": "DocumentMetadata",
      "name": "MyForm_001.png",
      "documentId": "549c4da2-ad3a-4f92-bfa2-019750a11007",
      "contentType": "FILE",
      "parentDocumentId": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "creationDateTime": "2023-01-04T10:56:49Z"
    },
    {
      "id": "DocumentMetadata::002",
      "type": "DocumentMetadata",
      "name": "MyForm_002.png",
      "documentId": "549c4da2-ad3a-4f92-bfa2-019750a11007",
      "contentType": "FILE",
      "parentDocumentId": "AttachedDocuments::77961b70-2071-4410-837a-436c908a4fa5",
      "creationDateTime": "2023-01-04T10:56:49Z"
    }

Every Data type document has only one AttachedDocuments document with parentDocId* field set to formId field of Data document.

If items in Data.dataValues has a document attached to it, AttachedDocuments.documents array have items with parentId field set to Data.dataValues[i].questionId.

Also every AttachedDocuments.documents[i] item has a DocumentMetadata document with id of AttachedDocuments.documents[i].id field.

I want to have a query which returns all Data.dataValues as an array but containing a field links that contains the DocumentMetadata.name field like below :

   [
    {
      "questionId": "Someguid123",
      "questionValue": "Question1",
      "links": ["MyForm_001.png", "MyForm_002.png"]
    },
    {
      "questionId": "Someguid",
      "questionValue": "Question2"
    },
    {
      "questionId": "AnotherGuid",
      "questionValue": "Question3"
    }
  ]

I tried unnest clause but couldn’t output datavalues items without documents. How should I write the query to include those also?

Thank you

2

Assuming you have a 1:1 relationship between Data & AttachedDocuments, you can try:

CREATE SCOPE default.f;
CREATE COLLECTION default.f.Data;
CREATE COLLECTION default.f.AttachedDocuments;
CREATE COLLECTION default.f.DocumentMetaData;

CREATE INDEX ix1 ON default.f.DocumentMetaData(id);

SELECT dataValues.questionId, dataValues.questionValue, links                                                                       
FROM default.f.Data join default.f.AttachedDocuments ON "MyFormData::"||Data.formId = AttachedDocuments.parentDocId
UNNEST Data.dataValues AS dataValues
LET links = (SELECT RAW DocumentMetaData.name
             FROM default.f.DocumentMetaData
             WHERE DocumentMetaData.parentDocumentId = AttachedDocuments.id
             AND id IN ARRAY a.id FOR a IN AttachedDocuments.documents WHEN a.parentId = dataValues.questionId END
            )
;

If you have a 1:n relationship between Data & AttachedDocuments but the attachments for a single question are wholly in a single attached document:

CREATE INDEX ix2 ON default.f.AttachedDocuments(parentDocId);
CREATE INDEX ix3 ON default.f.AttachedDocuments(id);

SELECT dataValues.questionId, dataValues.questionValue, links                                                                       
FROM default.f.Data join default.f.AttachedDocuments ON "MyFormData::"||Data.formId = AttachedDocuments.parentDocId
UNNEST Data.dataValues as dataValues
LET links = (SELECT RAW md.name
             FROM default.f.AttachedDocuments ad JOIN default.f.DocumentMetaData md ON ad.id = md.parentDocumentId
             UNNEST ad.documents d
             WHERE ad.parentDocId = "MyFormData::"||Data.formId
             AND d.id = md.id
             AND d.parentId = dataValues.questionId
            )
WHERE ANY dv IN AttachedDocuments.documents SATISFIES dv.parentId = dataValues.questionId END
;

If attachments for a single question can be spread over multiple attached documents, add a DISTINCT to the above statement.

HTH.

(You can use the same logic without collections adding appropriate aliasing and type field filtering.

2 Likes
3

Thank you so much @dh, it worked like a charm with some minor modifications.