Passing values from one (sub)query to another query as an input and fetch values

SQL++
, ,
1

In the below 1st JSON, I have to fetch all ‘code’ and ‘List’ where ‘override’ is “Y”. there can be ~100 saleWeekly entities, so ‘yearWeek’ can be list for each code.

{
“country”: “GBR”,
“field”: “sale”,
“source”: “onlineSale”,
“code”: “86401299”,
“_class”: “com.company.sale”,
“value”: {
“country”: “GBR”,
“saleWeekly”: [
{
“yearWeek”: “201906”,
“reasonCode”: “01”,
“override”: “Y”
},
{
“yearWeek”: “201913”,
“reasonCode”: “02”,
“override”: “N”
}
]
}
},
{
“country”: “GBR”,
“field”: “sale”,
“source”: “onlineSale”,
“code”: “86403421”,
“_class”: “com.company.sale”,
“value”: {
“country”: “GBR”,
“saleWeekly”: [
{
“yearWeek”: “201907”,
“reasonCode”: “01”,
“override”: “Y”
},
{
“yearWeek”: “201942”,
“reasonCode”: “01”,
“override”: “N”
}
]
}
}

whatever the codes I got from the above query, i have to fetch corresponding entities from below JSON files (aboveJSON.code == belowJSON.code).
and also under each code entity, I need only the yearWeek that I got from first query.
ex: if code 123 has yearweeks 201920, 201921 (from above queries) then from below JSON files, even if i saleWeekly has multiple entities, I need only for 201920, 201921 of the same code.
Field I need from below JSON - code, List, List, List

{
“country”: “GBR”,
“field”: “forecast”,
“source”: “allSale”,
“code”: “86401299”,
“_class”: “com.company.allSale”,
“value”: {
“country”: “GBR”,
“saleWeekly”: [
{
“yearWeek”: “201906”,
“promoIndicator”: “Y”,
“number”: 123
},
{
“yearWeek”: “201913”,
“promoIndicator”: “N”,
“number”: 234
}
]
}
},
{
“country”: “GBR”,
“field”: “forecast”,
“source”: “allSale”,
“code”: “86403421”,
“_class”: “com.company.allSale”,
“value”: {
“country”: “GBR”,
“saleWeekly”: [
{
“yearWeek”: “201907”,
“promoIndicator”: “N”,
“number”: 456
},
{
“yearWeek”: “201942”,
“promoIndicator”: “Y”,
“number”: 345
}
]
}
}

please provide me query for this. there can be ~20k JSON files like this that I have to query.

2 
SELECT f.code, ARRAY fw FOR fw IN f.`value`.saleWeekly WHEN fw.yearWeek IN s1.yws END AS saleWeekly
FROM (SELECT s.code, ARRAY sw.yearWeek FOR sw IN s.`value`.saleWeekly WHEN sw.override = "Y" END as yws
      FROM default AS s
      WHERE s.field = "sale"
            AND s._class = "com.company.sale"
            AND ANY sw IN s.`value`.saleWeekly SATISFIES sw.override = "Y" END) AS s1
JOIN default AS f ON s1.code = f.code AND f.field = "forecast"
                     AND f._class = "com.company.allSale"
                     AND ANY sw IN f.`value`.saleWeekly SATISFIES sw.yearWeek IN s1.yws END ;

CREATE INDEX ix1 ON default (DISTINCT ARRAY sw.override FOR sw IN `value`.saleWeekly END)
                        WHERE _class = "com.company.sale" AND field = "sale";

CREATE INDEX ix2 ON default (code) WHERE _class = "com.company.allSale" AND field = "forescast";