How can I capture the products from a shopping cart if it has 2 or more products selected, from the json with grail

[dff]

Hello, I have a concern.

I have the following Quuery. Which the product field brings in json, but separating it only allows me from the first block only

The idea is that I separate them into 2 or 3 results depending on the number of products that the json brings

attached query

fetch bizevents , from:now()-1h, to:now()
| filter event.type=="portalcmo.fyags.com/wpcmo"
| filter orderNumber=="2660"
| parse productos, "DATA json:json"
| fieldsAdd nombre = json[nombre], cantidad = json[cantidad], precio_unitario = json[precio_unitario] ,totalPedido,orderNumber
| fields productos,nombre, orderNumber

The idea is: 

which the 2 products correspond to the same order
nombre         Order 
Product1       1234

product2      1234

[cesarsaravia · Accepted solution]

Hi @dff 

You must have to use JSON_ARRAY instead of JSON, and then expand the rows.

data record(productos="""[
    {
      "nombre": "Basic Blue Jeans",
      "cantidad": 2,
      "precio_unitario": 30
    },
    {
      "nombre": "DNK Purple tshirt",
      "cantidad": 3,
      "precio_unitario": 45
    }
  ]""",orderNumber="2660", totalPedido=1500)
| filter orderNumber=="2660"
| parse productos,"JSON_ARRAY:json"
| expand json
| fieldsAdd nombre = json[nombre], cantidad = json[cantidad], precio_unitario = json[precio_unitario] ,totalPedido,orderNumber
| fields productos,nombre, orderNumber, totalPedido, cantidad, precio_unitario

Example: