Joining an Array of Elements in Analytics N1QL

Hi Guys,

Need your Analytics Query expertise.

I have this query below, My goal is to JOIN those doc ids from FROM statement without using WHERE IN

My Query:

SELECT
  META(products).id,
  products.name,
  products.rank,
  products.unitOfMeasure,
  products.price,
  products.upc,
  products.productCode
FROM [
  'product::0010',
  'product::0106',
  'product::0102',
  'product::0104',
  'product::0109',
  'product::0108',
  'product::0107'
] AS productIds
JOIN company_analytics.`products` AS `products`
  ON META(products).id IN productIds

Error

[
  {
    "code": 24057,
    "msg": "Type mismatch: expected value of type multiset or array, but got the value of type string (in line 19, at column 27)",
    "query_from_user": "SELECT\n  META(products).id,\n  products.name,\n  products.rank,\n  products.unitOfMeasure,\n  products.price,\n  products.upc,\n  products.productCode\nFROM [\n  'product::0010',\n  'product::0106',\n  'product::0102',\n  'product::0104',\n  'product::0109',\n  'product::0108',\n  'product::0107'\n] AS productIds\nJOIN company_analytics.`products` AS `products`\n  ON META(products).id IN productIds"
  }
]

Really, need your help thanks.

SELECT
  META(products).id,
  products.name,
  products.rank,
  products.unitOfMeasure,
  products.price,
  products.upc,
  products.productCode
FROM [
  'product::0010',
  'product::0106',
  'product::0102',
  'product::0104',
  'product::0109',
  'product::0108',
  'product::0107'
] AS productIds
JOIN company_analytics.`products` AS `products`
  ON META(products).id =  productIds;

FROM clause ARRAY constant iterates so each time productIds become value of ARRAY.(i.e. consider bucket is array of documents)

Hi @vsr1,

Oh… of course sorry I didn’t see that :frowning: Thanks man it works now. :slight_smile:

I have another concern @vsr1:
Is it possible to order / sort my result base on the ARRAY elements given ?

[
  'product::0010',
  'product::0106',
  'product::0102',
  'product::0104',
  'product::0109',
  'product::0108',
  'product::0107'
]

So currently, the results of the query above is like this:

[
  {
    "id": "product::0010",
    "name": "Product 0010",
    "price": 10.38,
    "productCode": "0010"
  },
  {
    "id": "product::0102",
    "name": "Product 0102",
    "price": 9.13,
    "productCode": "0102"
  },
  {
    "id": "product::0109",
    "name": "Product 0109",
    "price": 31.25,
    "productCode": "0109"
  },
  {
    "id": "product::0106",
    "name": "Product 0106",
    "price": 31.25,
    "productCode": "0106"
  },
  {
    "id": "product::0104",
    "name": "Product 0104",
    "price": 16.98,
    "productCode": "0104"
  },
  {
    "id": "product::0108",
    "name": "Product 0108",
    "price": 16.98,
    "productCode": "0108"
  },
  {
    "id": "product::0107",
    "name": "Product 0107",
    "price": 9.13,
    "productCode": "0107"
  }
]

I want the results to be like this:
Order / Sort base on the ARRAY elements.

[
  {
    "id": "product::0010",
    "name": "Product 0010",
    "price": 10.38,
    "productCode": "0010"
  },
    {
    "id": "product::0106",
    "name": "Product 0106",
    "price": 31.25,
    "productCode": "0106"
  },
  {
    "id": "product::0102",
    "name": "Product 0102",
    "price": 9.13,
    "productCode": "0102"
  },
    {
    "id": "product::0104",
    "name": "Product 0104",
    "price": 16.98,
    "productCode": "0104"
  },
  {
    "id": "product::0109",
    "name": "Product 0109",
    "price": 31.25,
    "productCode": "0109"
  },
  {
    "id": "product::0108",
    "name": "Product 0108",
    "price": 16.98,
    "productCode": "0108"
  },
  {
    "id": "product::0107",
    "name": "Product 0107",
    "price": 9.13,
    "productCode": "0107"
  }
]

Thanks, I appreciate your response. Really need help with this stuff.

There might be better way to do in the Analytics. One option will be

SELECT
  META(products).id,
  products.name,
  products.rank,
  products.unitOfMeasure,
  products.price,
  products.upc,
  products.productCode
FROM (SELECT ROW_NUMBER() OVER() AS rowid, productId
      FROM [
            'product::0010',
            'product::0106',
            'product::0102',
            'product::0104',
            'product::0109',
            'product::0108',
            'product::0107'
          ] AS productId) AS d
JOIN company_analytics.`products` AS `products`
  ON META(products).id =  d.productId
ORDER BY d.rowid;
1 Like

Hi @lyndondonz,

Please try the following query:

WITH productIds AS (
  SELECT pid, pos 
  FROM [
    'product::0010',
    'product::0106',
    'product::0102',
    'product::0104',
    'product::0109',
    'product::0108',
    'product::0107'
  ] AS pid AT pos
)  

SELECT
  META(products).id,
  products.name,
  products.rank,
  products.unitOfMeasure,
  products.price,
  products.upc,
  products.productCode
FROM productIds
JOIN company_analytics.`products` AS `products`
  ON META(products).id =  productIds.pid
ORDER BY productIds.pos;
1 Like

Hi guys @vsr1 and @dmitry.lychagin,

Thanks guys, both of your solution works :slight_smile: I’ve tested it and both of them had same execution time. Cool!