Get sum of field using multiple fields

SQL++
,
1

how to aggregate array items based on a field(s) in array ?
Ex: from below document, sum of values with respective country, size, and pattern.
[
{
“bpc”: “LSR000223”,
“country”: “JP”,
“id”: “MATERIAL::LSR000223::Z001”,
“pattern”: “R202Z”,
“size”: “195/85 R15 R202Z T”,
“totalStock”: “8942”,
“wsp”: “155.00”
},
{
“bpc”: “LSR008213”,
“country”: “JP”,
“id”: “MATERIAL::LSR008213::Z001”,
“pattern”: “R202Z”,
“size”: “195/85 R15 R202Z T”,
“totalStock”: “350”,
“wsp”: “155.00”
}
]

EXPECTED OUTPUT:
[
{
“bpc”: “LSR000223”,
“country”: “JP”,
“id”: “MATERIAL::LSR000223::Z001”,
“pattern”: “R202Z”,
“size”: “195/85 R15 R202Z T”,
“totalStock”: “9292”,
“wsp”: “155.00”
},
{
“bpc”: “LSR008213”,
“country”: “JP”,
“id”: “MATERIAL::LSR008213::Z001”,
“pattern”: “R202Z”,
“size”: “195/85 R15 R202Z T”,
“totalStock”: “9292”,
“wsp”: “155.00”
}
]

3 similar fields (size, pattern, country), then it will add the “totalStock” and it will be the value for items with similar fields stated.

can someone help me? thank you!

2

Hi @jrn,

  • totalStock is string, not sure how you want sum. sum can be done only on number
  • Do you need sum in the same document or across documents
  • Once you group based on size, pattern, country the documents will collapse. At the same time you will not be able to project other fields.

The Input and expected out don’t match. Please provide right info.

3

Hi @vsr1 ,

  • i just use TO_NUMBER function for totalStock
  • i need to sum across documents

Right now i am using this query:

SELECT  ARRAY_AGG(material.id) AS id, 
        ARRAY_AGG(material.materialCode) AS bpc, 
        ARRAY_AGG(material.size) AS size, 
        ARRAY_AGG(material.oldMaterialNumber) AS pattern,
        ARRAY_AGG(material.country) AS country,
        ARRAY_AGG(priceData.price) AS wsp,
        SUM(TO_NUMBER(stockData.totalStock)) AS totalStock
FROM bridgestone_test material
JOIN bridgestone_test stockData
ON KEYS material.stockId
JOIN bridgestone_test priceData
ON KEYS material.pricePrio3Id
WHERE material.docType = "MATERIAL"
GROUP BY material.size, material.oldMaterialNumber, material.country

I get this result:

{
      "bpc": [
        "LSR000223",
        "LSR008213"
      ],
      "country": [
        "JP",
        "JP"
      ],
      "id": [
        "MATERIAL::LSR000223::Z001",
        "MATERIAL::LSR008213::Z001"
      ],
      "pattern": [
        "R202Z",
        "R202Z"
      ],
      "size": [
        "195/85 R15     R202Z   T",
        "195/85 R15     R202Z   T"
      ],
      "totalStock": 9292,
      "wsp": [
        "155.00",
        "155.00"
      ]
    }

I was looking for another way to do it since i want the records to be separated by id, and bpc
thank you @vsr1

4

Hi @jrn ,
Try this . If needed use TO_STR(d.totalStock) AS totalStock

SELECT d.size, d.pattern, d.country, d.totalStock, u.*
FROM (SELECT material.size,
             material.oldMaterialNumber AS pattern,
             material.country,
             SUM(TO_NUMBER(stockData.totalStock)) AS totalStock,
             ARRAY_AGG( DISTINCT {material.id, "wsp": priceData.price, "bpc":material.materialCode}) AS arr
      FROM bridgestone_test  AS material
      JOIN bridgestone_test  AS stockData ON KEYS material.stockId
      JOIN bridgestone_test AS priceData ON KEYS material.pricePrio3Id
      WHERE material.docType = "MATERIAL"
      GROUP BY material.size, material.oldMaterialNumber, material.country) AS d
UNNEST d.arr AS u;

Is material.stockId, material.pricePrio3Id is ARRAY or single value?

5

This is genius. Problem solved! Now I can avoid data manipulation from front end. Thank you very much, @vsr1

6

@jrn ,
Is material.stockId, material.pricePrio3Id is ARRAY or single value?

7

@vsr1 sorry i didn’t read this below, it’s only single values

8

In that case you can also try this. It works like LEFT JOIN i.e. if no stock/price document also it gives material document

SELECT d.size, d.pattern, d.country, d.totalStock, u.*
FROM (SELECT material.size,
             material.oldMaterialNumber AS pattern,
             material.country,
             SUM(TO_NUMBER(totalStock)) AS totalStock,
             ARRAY_AGG( DISTINCT {material.id, wsp, "bpc":material.materialCode}) AS arr
      FROM bridgestone_test  AS material
      LET totalStock = (SELECT RAW stockData.totalStock FROM bridgestone_test AS stockData USE KEYS material.stockId)[0],
          wsp = (SELECT RAW priceData.price FROM bridgestone_test AS priceData USE KEYS material.pricePrio3Id)[0]
      WHERE material.docType = "MATERIAL"
      GROUP BY material.size, material.oldMaterialNumber, material.country) AS d
UNNEST d.arr AS u;
9

ohh noted! i will check this one too, thank you @vsr1 !
1 more question, should i also create index for this one?

10 
CREATE index ix1 ON bridgestone_test(country, oldMaterialNumber, size, id, materialCode, stockId,  pricePrio3Id ) 
WHERE docType = "MATERIAL";

https://index-advisor.couchbase.com/indexadvisor/#1

Also change WHERE material.docType = “MATERIAL” AND material.country IS NOT NULL

See if query covers

11

alright, will do. thanks @vsr1 for the huge help!

12

hi @vsr1 , i was just curious what can be the minimum execution time this query can get? right now at 5k records it’s taking up 5 to 6 secs with index. thank you.

13

It is hard to tell 5 to 6 sec might be long for 5k documents. If you are using EE
execute query in query work bench and goto Plan Text Tab and take look (This post has links how to analyze Filter taking too long - #4 by Marco_Greco)

If need help post that info.