Search code examples
node.jspostgresqltypeorm

Find in array of jsons postgres typeORM node js

Issue Description

@Entity({name: 'reports'}) export class Report extends BaseEntity {

@PrimaryGeneratedColumn()
public id: number;

@Column({name: 'dates_parsed', type: 'jsonb'})
public datesParsed: any;

}

image

I want to find all reports where day > 3.

Using SQL in PgAdmin I am able to find correct data using the next queries

SELECT  r.id, p ->> 'day' as day
FROM   "reports" r, jsonb_array_elements(r.dates_parsed) p
where (p->>'day')::int > 3

or

SELECT * FROM "reports" r
WHERE r.id IN (SELECT  t.id
FROM   "reports" t, jsonb_array_elements(t.dates_parsed) p
where (p->>'day')::int > 3)

We are not able use jsonb_array_elements in from it expects entityTarget

    const qb = this.createQueryBuilder(`reports`)
        .where(qb2 => {
            const subQuery = qb.subQuery()
                .select('report.id')
                .from(Report, 'report')
                .from('jsonb_array_elements(report.dates_parsed)', 'dates')
                .where(`(dates->>'day')::int > 3`)
                .getQuery();
            return `reports IN ` + subQuery;
        });

We can't create a query like this, because parsed is invisible in where

    const qb = this.createQueryBuilder(`reports`);
    qb.where(qb2 => {
        const subQuery = qb.subQuery()
            .select('report.id')
            .select('jsonb_array_elements(report.dates_parsed)', 'parsed')
            .from(Report, 'report')
            .where(`(parsed->>'day')::int > 3`)
            .getQuery();
        return `reports IN ` + subQuery;
    });

How to create a query using typeorm if I want to find data in my table. Where day equals 5

[ { "id": "1", "datesParsed": [ { "day": 6 }, { "day": 3 } ], "id": "2", "datesParsed": [ { "day": 10 }, { "day": 5 } ], ]

My Environment

Dependency Version
Operating System macos
Node.js version v14.5
Typescript version v4.0.3
TypeORM version v0.2.28

Additional Context

Relevant Database Driver(s)

  • [ ] aurora-data-api
  • [ ] aurora-data-api-pg
  • [ ] better-sqlite3
  • [ ] cockroachdb
  • [ ] cordova
  • [ ] expo
  • [ ] mongodb
  • [ ] mysql
  • [ ] nativescript
  • [ ] oracle
  • [x] postgres
  • [ ] react-native
  • [ ] sap
  • [ ] sqlite
  • [ ] sqlite-abstract
  • [ ] sqljs
  • [ ] sqlserver
Solution

  • One example of how to resolve it

    
const qb = this.createQueryBuilder(`reports`);
 
qb.andWhere((`reports.id IN (SELECT  t.id FROM   "reports" t, jsonb_array_elements(t.dates_parsed) p where (p->>'day')::int = 5)`));