TYPEORM filtering data of a complex JSON

Question

I am retrieving below data from Postgres using typeorm and I want to filter this data:

[{ "corporateid": 1001, "customerdetail": [{ "customerid": 1, 
"customertype": "CustType1", "products": [{ "productid": 1, "productcode": 
9000, "currencydetailss": [{ "currencydetailsid": 1, "ratetype": "Cash", 
"basecurrencyidFk": { "currencyid": 2, "currencyisocode": "IS0-9901", 
"currencyname": "INR" }, "currencyrates": [{ "currencyratesid": 1, 
"buyrate": 10.2, "sellrate":
11.2, "spotsell": 10.9 }, { "currencyratesid": 2, "buyrate": 98.2, 
"sellrate": 99.2, "spotsell": 98.9 }, { "currencyratesid": 3, "buyrate": 
11.2, "sellrate": 12.2, "spotsell": 11.9 }] }] }] }, { "customerid": 5, 
"customertype": "CustType5", "products": [] }, { "customerid": 4, 
"customertype": "CustType4", "products": [] }] }]

What I tried:

const details = await conn .getRepository(corporate) .find({ where: [ { corporateid: "1001" ,'customerdetail.customerid'= 2} ] });

I also tried LeftJoin and InnerJoin with where conditions: where: {corporateid: "1001",'customerdetail.customerid'= 2}

I tried multiple other ways.

Any help on how can I filter data from an JSON by passing optional params? For example: I might just pass corporateid or 'corporateid and customerid' and many similar combinations.

Answer 1

So I was able to do this via below code in-case anyone needs it:

let requestQueryParameters: { [key: string]: string } = {
       "c2": 'CustType3',
       "c3": '2'
   };


let queryCriteriaPlaceHolders: { [key: string]: string } = {
   "c2": "t2.c2= :c2",
   "c3": "t4.c1= :c1",
};

const query = await conn
           .getRepository(t1)
           .createQueryBuilder("t1")
           .leftJoinAndMapMany("t1.t2", t2, "t2", "t1.id = t2.id_fk")
           .leftJoinAndMapMany("t2.t3", t3, "t3", "t3.id_fk=t2.id")
           .leftJoinAndMapMany("t3.t4", t4, "t4", "t4.id_fk=t3.id")
           .leftJoinAndMapOne('t4.c1', t5, 't5', 't4.c1=t5.c1')
           .where("t1.id= :id", { id: 123 })
           .andWhere("t4.c2= :c2", { c2: 456 });

       Object.getOwnPropertyNames(requestQueryParameters).forEach(param => {
           let paramValue = requestQueryParameters[param];
           console.log("Key: " + param + " Value: " + paramValue);
           let reqObjLiteral = {} as any;
           reqObjLiteral[param] = paramValue
           query.andWhere(queryCriteriaPlaceHolders[param], reqObjLiteral)
       });

query.getMany();