Need a n1ql query and index to fetch attribute values that are deep inside documents and array

SQL++
, ,
1

Hi

Need help in writing a N1ql query and Index on how to fetch the subDocument with in the array whose intentId=“bd243aaf-ec21-4f53-8851-71c03e421662” nested deep under various keys?

Below is the sample document,

{
  "gtin": "07312040150700",
  "subClass": "W61DD",
  "currency": "GBP",
  "prices": {
    "trn:xxx:seller:uuid:8252e530-36d3-41f4-8e41-02d9579368de": {
      "48973981-9008-48ed-8571-b2d708f135d0": [
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "ad243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1649890800000,
          "sellingUOM": "EACH",
          "basePrice": "26.26",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        },
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "bd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1749890800000,
          "sellingUOM": "EACH",
          "basePrice": "27.05",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        }
      ],
      "18973981-9008-48ed-8571-b2d708f135d0": [
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "ad243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1649890800000,
          "sellingUOM": "EACH",
          "basePrice": "26.26",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        },
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "bd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1749890800000,
          "sellingUOM": "EACH",
          "basePrice": "27.05",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        }
      ]
    },
    "trn:xxx:seller:uuid:8252e530-36d3-41f4-8e41-02d9579368db": {
      "48973981-9008-48ed-8571-b2d708f135d0": [
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "cd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1649890800000,
          "sellingUOM": "EACH",
          "basePrice": "25.00",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        },
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "dd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1849890800000,
          "sellingUOM": "EACH",
          "basePrice": "22.00",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        }
      ],
      "18973981-9008-48ed-8571-b2d708f135d0": [
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "cd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1649890800000,
          "sellingUOM": "EACH",
          "basePrice": "25.00",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        },
        {
          "prodId": "081882822",
          "itemNumber": "081882822",
          "intentId": "dd243aaf-ec21-4f53-8851-71c03e421662",
          "effectiveDateTimeOffset": "Z",
          "effectiveDateTime": 1849890800000,
          "sellingUOM": "EACH",
          "basePrice": "22.00",
          "state": "APPROVED",
          "createdDateTime": 1713801491892,
          "updatedDateTime": 1721400805300,
          "inheritedEffectiveDateTime": 1428879600000,
          "gtinDeletionDate": 1428879600000
        }
      ]
    }
  },
  "_class": "com.xxx.api.price.domain.entity.Price"
}

Regards,
Venkat

2

You can get your result with something like this:

SELECT uuu
FROM default d
UNNEST object_values(d.prices) u
UNNEST object_values(u) uu
UNNEST uu uuu
WHERE uuu.intentId = "bd243aaf-ec21-4f53-8851-71c03e421662"

for which ADVISE suggests:

CREATE INDEX adv_ALL_object_values_d_prices_object_values_u_uu_uuu ON `default`(
ALL ARRAY (
    ALL ARRAY (
        ALL ARRAY `uuu` FOR uuu IN `uu` END
    ) FOR uu IN object_values(`u`) END
) FOR u IN object_values((`prices`)) END
)

but just based on the nesting expansion it could be quite large, so you’d have to evaluate it carefully.

HTH.

1 Like
3

@dh
Thanks for the response, will try it out and update you. However, I have small query as I understand from the index I see uuu is actually indexing the complete subDocument instead of a specific field like intentId. Please correct me if my understanding is incorrect?

4 

CREATE INDEX adv_ALL_object_values_d_prices_object_values_u_uu_uuu ON `default`(
ALL ARRAY (
    ALL ARRAY (
        ALL ARRAY `uuu`.intentId FOR uuu IN `uu` END
    ) FOR uu IN object_values(`u`) END
) FOR u IN object_values((`prices`)) END
)
1 Like
5

Thanks @vsr1 it worked.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.