Joining an Array of Elements in Analytics N1QL

Analytics
, ,
1

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.

2 
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)

3

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.

4

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
5

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
6

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!