Support for querying JSON data info

[crutchcorn]

Issue type:

[ ] question
[ ] bug report
[x] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[x] mssql
[x] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

It would be nice to be able to have a single API to be able to query information from a property in the JSON data stored in a mysql / mariadb / postgres database, as they all seem to have their own APIs.

[pleerock]

query information from a property in the JSON data stored

can you please explain what do you call JSON data here?

[thldev]

If you store JSON data in specific JSON typed columns then you can filter and select on the nested JSON values.

This article gives an overview of how to do it with Postgres.
(http://schinckel.net/2014/05/25/querying-json-in-postgres)

[crutchcorn]

Exactly as @thldev said. I'm currently using Raw (without passing user input, obviously) and some manual syntax for MySQL/Postgres, but it is unfortunate because I have to write the queries for both by hand (as the syntax differs)

[mudkipme]

I store various JSONB data in Postgres. It would be nice to use

const user = await getRepository(User).findOne({
  profile: {
    id: profile.id,
    provider: profile.provider,
  },
});

Instead of:

const user = await getRepository(User).createQueryBuilder()
  .where('profile @> :profile', {
    profile: {
      id: profile.id,
      provider: profile.provider,
    },
  }).getOne();

[anymost]

The same issue I encountered, Wish Supporting for querying JSON data info

[kowsheek]

Further to supporting queries, other functionalities such as @Column and its features should be there for embedded json or jsonb entities

[fan-tom]

The main problem for me is that you should know the type of values when querying json data to properly pass them.
For example, if myValue is jsonb column, such query will raise db error, because arrays must be converted to jsonb first

type MyValueType = string | number | object | any[];
function queryByMyValue(myValue: MyValueType) {
...
 .andWhere('myEntityAlias.myValue = :myValue', { myValue })
...
}
queryByMyValue(['foo','bar','baz']);

Error:

query failed: SELECT ... WHERE ... AND "myEntityAlias"."my_value" = $1 -- PARAMETERS: [["foo","bar","baz"]]
error: error: invalid input syntax for type json
    at Connection.parseE (.../node_modules/pg/lib/connection.js:606:11)
    at Connection.parseMessage (.../node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (.../node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:203:13)
    at Socket.EventEmitter.emit (domain.js:471:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:210:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:166:17) {
  name: 'error',
  length: 174,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected ":", but found ",".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {"foo",...',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'json.c',
  line: '1203',
  routine: 'report_parse_error'
}

[bennycode]

I have the following entity structure:

export interface MessengerSetup {
  client: MessengerClient;
  creatorId: string;
}

@Entity()
export class SignalEntity extends BaseEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column('simple-json')
  messengerSetup: MessengerSetup;

  constructor(messengerSetup: MessengerSetup) {
    super();
    this.messengerSetup = messengerSetup;
  }
}

I wish I could do:

async findEntities(client: MessengerClient, creatorId: string) {
  const records = await SignalEntity.find({messengerSetup: {client, creatorId}});
  // ...
}

Since it does not work I am filtering on the client-side as a workaround which is bad since I need to query everything from the database in the first place:

async findEntities(client: MessengerClient, creatorId: string) {
  const records = await SignalEntity.find();
  const filtered = records.filter(record => record.messengerSetup.client === client && record.messengerSetup.creatorId === creatorId);
  // ...
}

[rhlsthrm]

Since it does not work I am filtering on the client-side as a workaround which is bad since I need to query everything from the database in the first place

You can still do it in the DB query, you just have to use the raw SQL. I do something like:

const res = await this.createQueryBuilder("app_instance")
  .leftJoinAndSelect(
    AppRegistry,
    "app_registry",
    "app_registry.appDefinitionAddress = app_instance.appDefinition",
  )
  .leftJoinAndSelect("app_instance.channel", "channel")
  .where("app_registry.name = :name", { name: SimpleLinkedTransferAppName })
  .andWhere(`app_instance."latestState"::JSONB @> '{ "paymentId": "${paymentId}" }'`)
  .andWhere(
    `app_instance."latestState"::JSONB #> '{"coinTransfers",0,"to"}' = '"${senderSignerAddress}"'`,
  )
  .getOne();

This works great, but I would much prefer a real syntax.

[ivanproskuryakov]

it would be great to have the support for jsonb field within the usual find which is a joy to use with {eager: true} option.

export class Product extends AbstractEntity {
  @Column('jsonb')
  body: string | any;

  @OneToMany(() => ProductImage, productImage => productImage.product, {eager: true})
  productImages: ProductImage[];
}

so the ideal query would look like this(not possible now)

public async findByUser(user: User, filter: IBCProductFilter): Promise<Product[]> {
    return getRepository(Product)
    .find({
      where: {
        user,
        "body @> :body": {body: filter},
      },
    });
  }

instead it requires workaround with createQueryBuilder

  public async findByUser(user: User, filter: IBCProductFilter): Promise<Product[]> {
    const productIds = await getRepository(Product)
    .createQueryBuilder('p')
    .select('p.id')
    .leftJoin("p.user", "u")
    .where("u.id = :userId", {userId: user.id})
    .andWhere("body @> :body", {body: filter})
    .getMany();
    const ids = productIds.map(p => p.id);

    return getRepository(Product).findByIds(ids);
  }

[imnotjames]

As said above - this can be done with the Raw operator. The Raw operator in the upcoming 0.2.29 release will also support input parameters.

Still, a more explicitly defined way to access it would be cool.

[filipmacek]

How to use this in SELECT, when I wan to access some property of json column/object??

[pleerock]

We already support @>, <@, &&` operators in the latest 0.3.x version via #8766 .

We can add additional json operators the same way (contributions are welcomed btw).

Obviously we cannot support super complex stuff via FindOptions, everything else requested in this issue is possible via the QueryBuilder.

Can one create a good proposal for the feature(-s) requested in this thread?