Sorting array element and fetch limited elements

Hi,
I have the below JSON data:
{
“itemCategory”:“Cat1”,
“itemCode”:“code1”,
“itemName”:“Name1”,
“sellingPrice”:[
{ “pricePerUnit”:120,
“currency”:“Rupees”,
“validFrom”:“2024-01-01 00:00:00”
“validTo”:“2024-01-05 00:00:00”
},
{ “pricePerUnit”:130,
“currency”:“Rupees”,
“validFrom”:“2024-01-05 00:00:00”
“validTo”:“2024-01-10 00:00:00”
},

                   { "pricePerUnit":130,
                   "currency":"Rupees",
                   "validFrom":"2024-01-15 00:00:00"
				   "validTo":"2024-01-20 00:00:00"
                  },
				  { "pricePerUnit":130,
                   "currency":"Rupees",
                   "validFrom":"2024-01-25 00:00:00"
				   "validTo":"9999-12-31 59:59:59"
                  }
                  ]

}

I want to fetch the latest 2 prices based on validFrom date along with itemCode and itemName,

That is i want the output as below:

{

"itemCode":"code1",
"itemName":"Name1",
"sellingPrice":[
				  { "pricePerUnit":130,
                   "currency":"Rupees",
                   "validFrom":"2024-01-25 00:00:00"
				   "validTo":"9999-12-31 59:59:59"
                  },
				  { "pricePerUnit":130,
                   "currency":"Rupees",
                   "validFrom":"2024-01-15 00:00:00"
				   "validTo":"2024-01-20 00:00:00"
                  },
                  ]

}

Also, please note that, the array element is not fixed and dynamic.

You could try, assuming you want the whole of the array element:

SELECT b.itemCode
      ,b.itemName
      ,(SELECT sp.* FROM b.sellingPrice sp ORDER BY sp.validFrom DESC LIMIT 2) AS latestsPrices
FROM myBucket b
WHERE ...

else of course you could simply alter the inner SELECT’s projection to include only the fields you want.

If you don’t want an array containing the latest two prices you could do something like:

SELECT b.itemCode                                                                                                                   
      ,b.itemName
      ,sellingPrice[0].pricePerUnit AS latestPrice
      ,sellingPrice[0].currency AS latestCurency
      ,sellingPrice[1].pricePerUnit AS previousPrice
      ,sellingPrice[1].currency AS previousCurency
FROM default b
LET sellingPrice = (SELECT sp.* FROM b.sellingPrice sp ORDER BY sp.validFrom DESC LIMIT 2)
WHERE ...

etc.

HTH.

1 Like

Thank you for the solution. This worked!