Sub Array Document Search Problem

SQL++
, ,
1

Hi again :slight_smile:

{
  "attributeValues": [
    {
      "attributeCustomValue": "",
      "attributeId": 10,
      "attributeName": "Mevsim",
      "attributeValueId": 14515,
      "attributeValueName": "Yaz",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709867",
      "id": "c7e37b791a864cf9a9c9e0328ce692cc",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709868"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 12,
      "attributeName": "Desen",
      "attributeValueId": 18,
      "attributeValueName": "Düz Renk",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709872",
      "id": "e37b384780314ee29aede406b9da8769",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709873"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 19,
      "attributeName": "Stil",
      "attributeValueId": 2520,
      "attributeValueName": "Trend",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709877",
      "id": "259ec8a1f57b429d9e9ef36ed3ad3989",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709878"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 20,
      "attributeName": "Kullanım Alanı",
      "attributeValueId": 356,
      "attributeValueName": "Günlük",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709891",
      "id": "c7fcea60527a41169d1811ebc2acaa74",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709892"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 23,
      "attributeName": "Yaka Tipi",
      "attributeValueId": 14640,
      "attributeValueName": "O Yaka",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709914",
      "id": "265539d1c4bb4564b70538c6a4ab8605",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709915"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 29,
      "attributeName": "Kol Boyu",
      "attributeValueId": 821,
      "attributeValueName": "Uzun Kol",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709919",
      "id": "dfb8dc9c254d41acb5701c0135f17b4a",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709920"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 1,
      "attributeName": "Kumaş / İplik Özelliği",
      "attributeValueId": 15116,
      "attributeValueName": "Pamuk Elastan",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709924",
      "id": "0f167aa4472447b380fd89843387f513",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709925"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 184,
      "attributeName": "Kumaş Tipi",
      "attributeValueId": 14721,
      "attributeValueName": "Örme",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709935",
      "id": "0da531ad28b849009d19287a85439b44",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709936"
    },
    {
      "attributeCustomValue": "",
      "attributeId": 196,
      "attributeName": "Kalıp",
      "attributeValueId": 14946,
      "attributeValueName": "Dar/Slim",
      "createdBy": "System",
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6709941",
      "id": "c811bdbfefde40368d33fcee4fb37cb9",
      "isActive": true,
      "isCustom": false,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T00:00:00Z",
      "updatedTime": "13:32:27.6709941"
    }
  ],
  "barcode": "8682945508944",
  "brandId": 1,
  "brandName": "Tommy Life",
  "categories": [
    {
      "categoryId": 12224,
      "categoryName": "Spor Eşofman Üstü",
      "categoryParentId": 12216,
      "isLast": true,
      "languageCode": "tr"
    },
    {
      "categoryId": 12216,
      "categoryName": "Aktif Spor Giyim",
      "categoryParentId": 0,
      "isLast": false,
      "languageCode": "tr"
    },
    {
      "categoryId": 12278,
      "categoryName": "Tommy Life 8 Mart Kampanyası",
      "categoryParentId": 12274,
      "isLast": false,
      "languageCode": "tr"
    },
    {
      "categoryId": 12274,
      "categoryName": "8 Mart Dünya Kadınlar Günü Kampanyası",
      "categoryParentId": 491,
      "isLast": false,
      "languageCode": "tr"
    },
    {
      "categoryId": 491,
      "categoryName": "Kampanyalar",
      "categoryParentId": 0,
      "isLast": false,
      "languageCode": "tr"
    }
  ],
  "categoryHierarchy": "Aktif Spor Giyim > Spor Eşofman Üstü",
  "categoryId": 12224,
  "categoryName": "Spor Eşofman Üstü",
  "commentCount": 0,
  "commentRate": 0,
  "createdBy": null,
  "createdDate": "2023-01-27T00:00:00Z",
  "createdTime": "13:32:27.6708101",
  "favoriteCount": 0,
  "groupId": 252,
  "gtin": "",
  "id": "0001fceed2854f04b6c225939a266bd5",
  "images": [
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/8315f9ba-99da-4fc1-ae5d-176dbf343100",
      "sort": 1,
      "storageId": "8315f9ba-99da-4fc1-ae5d-176dbf343100"
    },
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/90a32f8b-737e-43a9-8467-786330c58600",
      "sort": 2,
      "storageId": "90a32f8b-737e-43a9-8467-786330c58600"
    },
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/32f9021a-81b1-498e-c678-75c22edd1d00",
      "sort": 3,
      "storageId": "32f9021a-81b1-498e-c678-75c22edd1d00"
    },
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/69753acd-9480-4de6-08f1-367bdeb48000",
      "sort": 4,
      "storageId": "69753acd-9480-4de6-08f1-367bdeb48000"
    },
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/0b3e9e2b-9d2a-4317-d7ca-f644244e8d00",
      "sort": 5,
      "storageId": "0b3e9e2b-9d2a-4317-d7ca-f644244e8d00"
    },
    {
      "baseUrl": "https://imagedelivery.net",
      "fileName": "",
      "link": "https://imagedelivery.net/1hk-dCcnLxTD8t9bYUmTyQ/a76c802b-5ae1-49ec-2ccc-231733ebcb00",
      "sort": 6,
      "storageId": "a76c802b-5ae1-49ec-2ccc-231733ebcb00"
    }
  ],
  "isActive": true,
  "isDeleted": false,
  "langDetails": [
    {
      "createdBy": null,
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6708119",
      "descriptionHtml": "Ürün kodu : 97150_07 Ürün adı : Indigo kadın uzun kollu dar kesim o yaka crop - 97150 Modelin ölçüleri : boy : 1.78 cm / göğüs : 83 cm / bel : 62 cm / kalça : 91 cm Numune bedeni : s / 36 Kumaş bilgisi : %92 pamuk %8 elastan Mevsimi : Yaz Ürün kalıp bilgisi : dar kalıp Paket/ürün içeriği : Crop top Yıkama talimatı : 30 derecede benzer renkler ile yıkanmalıdır.",
      "id": "95abc1755f8e4c938256921b33d1cb6a",
      "isActive": true,
      "isDeleted": false,
      "languageCode": "tr",
      "languageName": "Türkçe",
      "metaDescription": "",
      "metaKeywords": "",
      "metaTitle": "",
      "name": "Kadın Indigo Uzun Kollu Dar Kesim O Yaka Crop - 97150",
      "sort": 0,
      "updatedBy": null,
      "updatedDate": "0001-01-01T00:00:00",
      "updatedTime": "00:00:00",
      "url": "kadin-indigo-uzun-kollu-dar-kesim-o-yaka-crop-97150-p-8554"
    }
  ],
  "mpn": "T10BY-97150",
  "productChange": null,
  "productId": 8554,
  "reviewCount": 0,
  "sku": "T10BY-97150",
  "statusId": 2,
  "statusName": "Onaylandı",
  "statusNote": null,
  "totalCount": 0,
  "updatedBy": null,
  "updatedDate": "0001-01-01T00:00:00",
  "updatedTime": "00:00:00",
  "variantMerchants": [
    {
      "barcode": "8682945508944",
      "commissionRate": 20,
      "createdBy": "System",
      "createdDate": "2023-01-27T13:32:27.6720059Z",
      "createdTime": "13:32:27.6720060",
      "desi": 0,
      "height": 0,
      "id": "7ed08a20825a482d963ea6549ebec182",
      "isActive": true,
      "isDeleted": false,
      "length": 0,
      "listPrice": 174.9,
      "merchantId": 4701,
      "merchantName": "Tommy Life",
      "returnAddressId": 170,
      "salePrice": 174.9,
      "shipmentAddressId": 170,
      "shipmentDuration": 0,
      "statusId": 2,
      "statusName": "Confirmed",
      "stock": 61,
      "stockCode": "",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T13:32:27.6720061Z",
      "updatedTime": "13:32:27.6720061",
      "variantValueId": 2,
      "variantValueMerchantId": 1003,
      "variantValueName": "S",
      "weight": 0,
      "width": 0
    },
    {
      "barcode": "8682945508937",
      "commissionRate": 20,
      "createdBy": "System",
      "createdDate": "2023-01-27T13:32:27.6720078Z",
      "createdTime": "13:32:27.6720079",
      "desi": 0,
      "height": 0,
      "id": "75d0427f7e654dc08ec845505645f701",
      "isActive": true,
      "isDeleted": false,
      "length": 0,
      "listPrice": 174.9,
      "merchantId": 4701,
      "merchantName": "Tommy Life",
      "returnAddressId": 170,
      "salePrice": 174.9,
      "shipmentAddressId": 170,
      "shipmentDuration": 0,
      "statusId": 2,
      "statusName": "Confirmed",
      "stock": 46,
      "stockCode": "",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T13:32:27.6720079Z",
      "updatedTime": "13:32:27.6720079",
      "variantValueId": 3,
      "variantValueMerchantId": 1004,
      "variantValueName": "M",
      "weight": 0,
      "width": 0
    },
    {
      "barcode": "8682945508920",
      "commissionRate": 20,
      "createdBy": "System",
      "createdDate": "2023-01-27T13:32:27.6720088Z",
      "createdTime": "13:32:27.6720088",
      "desi": 0,
      "height": 0,
      "id": "bb5021b36b9943818451f1fe48435fbc",
      "isActive": true,
      "isDeleted": false,
      "length": 0,
      "listPrice": 174.9,
      "merchantId": 4701,
      "merchantName": "Tommy Life",
      "returnAddressId": 170,
      "salePrice": 174.9,
      "shipmentAddressId": 170,
      "shipmentDuration": 0,
      "statusId": 2,
      "statusName": "Confirmed",
      "stock": 32,
      "stockCode": "",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T13:32:27.6720089Z",
      "updatedTime": "13:32:27.6720089",
      "variantValueId": 4,
      "variantValueMerchantId": 1005,
      "variantValueName": "L",
      "weight": 0,
      "width": 0
    },
    {
      "barcode": "8682945508951",
      "commissionRate": 20,
      "createdBy": "System",
      "createdDate": "2023-01-27T13:32:27.6720097Z",
      "createdTime": "13:32:27.6720097",
      "desi": 0,
      "height": 0,
      "id": "4d78e8a940b74673a675e126b3973611",
      "isActive": false,
      "isDeleted": false,
      "length": 0,
      "listPrice": 174.9,
      "merchantId": 4701,
      "merchantName": "Tommy Life",
      "returnAddressId": 170,
      "salePrice": 174.9,
      "shipmentAddressId": 170,
      "shipmentDuration": 0,
      "statusId": 5,
      "statusName": "OutOfStock",
      "stock": 0,
      "stockCode": "",
      "updatedBy": "System",
      "updatedDate": "2023-01-27T13:32:27.6720098Z",
      "updatedTime": "13:32:27.6720098",
      "variantValueId": 5,
      "variantValueMerchantId": 1006,
      "variantValueName": "XL",
      "weight": 0,
      "width": 0
    }
  ],
  "variants": [
    {
      "barcode": "8682945508944",
      "createdBy": null,
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6715781",
      "id": "dfe3af6cc40744a09e7574e66df26abe",
      "isActive": true,
      "isDeleted": false,
      "languageCode": null,
      "languageName": null,
      "sort": 0,
      "updatedBy": null,
      "updatedDate": "0001-01-01T00:00:00",
      "updatedTime": "00:00:00",
      "variantValueId": 2,
      "variantValueName": "S"
    },
    {
      "barcode": "8682945508937",
      "createdBy": null,
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6715789",
      "id": "1c2cef1f2a644bb7a6fc8a06010cfb79",
      "isActive": true,
      "isDeleted": false,
      "languageCode": null,
      "languageName": null,
      "sort": 0,
      "updatedBy": null,
      "updatedDate": "0001-01-01T00:00:00",
      "updatedTime": "00:00:00",
      "variantValueId": 3,
      "variantValueName": "M"
    },
    {
      "barcode": "8682945508920",
      "createdBy": null,
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6715793",
      "id": "5807f7f6f6cd4aa1981e0af8f4ad585b",
      "isActive": true,
      "isDeleted": false,
      "languageCode": null,
      "languageName": null,
      "sort": 0,
      "updatedBy": null,
      "updatedDate": "0001-01-01T00:00:00",
      "updatedTime": "00:00:00",
      "variantValueId": 4,
      "variantValueName": "L"
    },
    {
      "barcode": "8682945508951",
      "createdBy": null,
      "createdDate": "2023-01-27T00:00:00Z",
      "createdTime": "13:32:27.6715797",
      "id": "ae8355816c534cca93ba23a69bbbf549",
      "isActive": true,
      "isDeleted": false,
      "languageCode": null,
      "languageName": null,
      "sort": 0,
      "updatedBy": null,
      "updatedDate": "0001-01-01T00:00:00",
      "updatedTime": "00:00:00",
      "variantValueId": 5,
      "variantValueName": "XL"
    }
  ],
  "vat": 8
}

I’m sharing my product model.
We have 33k products and its increasing everyday.
I need to search “products.variantMerchants.variantValueMerchantId” with IN.
First query;
Taking 2.3 seconds.
SELECT
t.productId
,vM.variantValueMerchantId
,vM.salePrice
,vM.stock
FROM B2CData._default.Products as t
UNNEST t.variantMerchants vM
WHERE vM.variantValueMerchantId IN [{variantValueMerchantIds}]
Second SEARCH;
Search Index;

resim
resim714×842 42.7 KB

Using;
its take 20 seconds and cant get data from meta

SELECT  t.productId ,smeta.fields.variantMerchants.stock
                FROM B2CData._default.Products as t
                let smeta=search_meta()
               WHERE SEARCH(t,
               {'indexName':'variantValueMerchantIdIndex',"fields": ["*"], "score": "none",
               'query':{'query':'variantMerchants.variantValueMerchantId:10016'}})

resim
resim1236×455 26.2 KB

2

Issue might be smeta.fields.variantMerchants.stock
Sub fileds in FTS store might be stored as flatten
Try smeta.fields.* and see how it look and change accordingly.
I assume you may need like this.

SELECT  t.productId ,smeta.fields.`variantMerchants.stock`
                FROM B2CData._default.Products as t
                let smeta=search_meta()
               WHERE SEARCH(t,
               {'indexName':'variantValueMerchantIdIndex',"fields": ["*"], "score": "none",
               'query':{'query':'variantMerchants.variantValueMerchantId:10016'}})

cc @abhinav

Also

GSI 7.1+

CREATE INDEX ix1 ON B2CData._default.Products( ALL ARRAY FLATTEN_KEYS(vm.variantValueMerchantId, vm.stock, vm.salePrice)
                                                   FOR vm IN variantMerchants END, productId);
SELECT t.productId,
       vm.variantValueMerchantId,
       vm.salePrice,
       vm.stock
FROM B2CData._default.Products AS t
UNNEST t.variantMerchants vm
WHERE vm.variantValueMerchantId IN [ 16102, 16103];
1 Like
3

@vsr1’s recommendation above is good.
Couple slight adjustments however -

SELECT smeta.fields.productId, smeta.fields.`variantMerchants.stock`
FROM B2CData._default.Products AS t
let smeta=search_meta()
WHERE SEARCH(t,{"query": {"min": 1006, "max": 1006, "inclusive_min": true, "inclusive_max": true, "field": "variantMerchants.variantValueMerchantId"}, "fields": ["*"], "score": "none"})

Note that this query will retrieve productId from the search index as well and not involve fetching from KV. stock is a number within several subobjects of variantMerchants, so it’d be flattened. Here’s how output would look for the query with the document you’ve shared.

[
  {
    "productId": 8554,
    "variantMerchants.stock": [
      61,
      46,
      32,
      0
    ]
  }
]

Also note that I didn’t use a query string query for the numeric lookup (but instead used the numeric range query), because it seems I ran into some formatting issue for numeric searches via a query string which caused the engine to look for a text field stock as opposed to numeric within the search index.

1 Like
4

Hi,
Thanks for your reply its work like a charm.
The query now takes 0,70ms.

5

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