CODEWITHSUNDEEP
nosqlcouchbasesql++
Jade Brillantes
Jade Brillantes

Reputation: 31

Select all values inside different arrays inside an array

I have a document that looks like this:

  "userName": "sample name",
  "values": [
    {
      "values": [
        {
          "brand": "SOLIGNUM CLEAR",
          "name": "Solignum Colourless AZ",
          "price": "569",
          "qip": "30.00",
          "sku": "1L",
          "unit": "Piece"
        }
      ]
    },
    {
      "values": [
        {
          "brand": "FirePRO",
          "name": "FirePRO",
          "price": "419.75",
          "qip": "30.00",
          "sku": "1L",
          "unit": "Cartons"
        },
        {
          "brand": "SOLIGNUM AEROSOL",
          "name": "Solignum Colourless AZ Aerosol",
          "price": "397",
          "qip": "30.00",
          "sku": "500ML",
          "unit": "Piece"
        }
      ]
    }
  ]

My query looks like this:

SELECT orders.unit, orders.sku, orders.name, orders.srp, TONUMBER(orders.price) AS price, orders.qip as quantity
FROM jdi stoCallLog
UNNEST stoCallLog.`values`[0].`values` AS orders

Query result looks like this

I have tried changing the unnest block into this:

UNNEST stoCallLog.`values`[1].`values` AS orders

selects only the 2nd array value

Also like this: 

UNNEST stoCallLog.`values`.`values` AS orders

not possible i guess, it returns none

I need a way to select all of the values at once. Is there any way to do it?

Upvotes: 2

Views: 31

Answers (1)

Jade Brillantes
Jade Brillantes

Reputation: 31

Solved by modifying the UNNEST block to:

UNNEST `values` as rawOrders 
UNNEST rawOrders.`values` as orders

Upvotes: 1

Related Questions