Search through json array

[bekanur98]

Describe the bug

I have two tables, one table is users, the other is books, and in the books table I have a "parameters" field that is stored in JSON format

{
  "seasons": [
    {"name": "autumn"},
    {"name": "winter"},
    {"name": "spring"},
    {"name": "summer"}
  ]
}

and I want to get users who have books wherein the parameters the season name should equal "summer".

Simple SQL looks like this:

SELECT u.*
FROM users u
JOIN books b ON u.id = b.user_id
WHERE b.parameters->'season' @> '[{"name": "summer"}]'::jsonb;

So by docs mikro-orm I did next:

const entityManager = orm.em.fork(); // Замени 'orm' на инстанс MikroORM в твоем приложении

const usersWithSummerBooks = await entityManager.find(
  User,
  { books: { parameters: { season: { $contains: [{ name: 'summer' }] } } } },
  { populate: ['books'] },
);

but mikro-orm converting this where condition to this:

where b.parameters->>''season` @>  '[{"name": "summer"}]'

and I got sql error:

[42883] ERROR: operator does not exist: text @> jsonb Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

The main difference between simple sql and mikro-orm converted sql is ->> and ->.

Reproduction

// user.entity.ts
import { Entity, PrimaryKey, Property, OneToMany } from '@mikro-orm/core';
import { Book } from './book.entity';

@Entity()
export class User {
  @PrimaryKey()
  id!: number;

  @OneToMany(() => Book, (book) => book.user)
  books = new Collection<Book>(this);

  // another fields of user
}

// book.entity.ts
import { Entity, PrimaryKey, Property, ManyToOne } from '@mikro-orm/core';
import { User } from './user.entity';

@Entity()
export class Book {
  @PrimaryKey()
  id!: number;

  @Property({ type: 'jsonb' })
  parameters!: BooksParameters;

  @ManyToOne(() => User)
  user!: User;

  // another fields of book
}

interface BooksParameters {
  seasons: SeasonType[];
}

interface SeasonType {
  name: string;
}

import { EntityManager } from '@mikro-orm/core';

// service
const entityManager = orm.em.fork(); // Замени 'orm' на инстанс MikroORM в твоем приложении

const usersWithSummerBooks = await entityManager.find(
  User,
  { books: { parameters: { season: { $contains: [{ name: 'summer' }] } } } },
  { populate: ['books'] },
);

console.log(usersWithSummerBooks);

What driver are you using?

@mikro-orm/postgresql

MikroORM version

"@mikro-orm/postgresql": "^5.7.14"

Node.js version

v18.18.2

Operating system

Linux Mint

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
• The provided reproduction is a minimal reproducible example of the bug.

[B4nan]

Unfortunately can't provide github repo - private repo

Your repo is not a reproduction. I want to see a minimal code to reproduce your problem, I am not interested in checking your actual app in your private repo. Please provide at least the entity definitions.

"@mikro-orm/postgresql": "^5.7.14"

And be sure to upgrade, you should actually do that before you report anything, as the issue might have been already solved.

[bekanur98]

// user.entity.ts
import { Entity, PrimaryKey, Property, OneToMany } from '@mikro-orm/core';
import { Book } from './book.entity';

@Entity()
export class User {
  @PrimaryKey()
  id!: number;

  @OneToMany(() => Book, (book) => book.user)
  books = new Collection<Book>(this);

  // another fields of user
}

// book.entity.ts
import { Entity, PrimaryKey, Property, ManyToOne } from '@mikro-orm/core';
import { User } from './user.entity';

@Entity()
export class Book {
  @PrimaryKey()
  id!: number;

  @Property({ type: 'jsonb' })
  parameters!: BooksParameters;

  @ManyToOne(() => User)
  user!: User;

  // another fields of book
}

interface BooksParameters {
  seasons: SeasonType[];
}

interface SeasonType {
  name: string;
}

//service
import { EntityManager } from '@mikro-orm/core';

const entityManager = orm.em.fork(); // Замени 'orm' на инстанс MikroORM в твоем приложении

const usersWithSummerBooks = await entityManager.find(
  User,
  { books: { parameters: { season: { $contains: [{ name: 'summer' }] } } } },
  { populate: ['books'] },
);

console.log(usersWithSummerBooks);

[B4nan]

Thanks, gave it a quick look and this is actually more broken in v6, will first fix that and see if I can also backport the fix to 5.x, but I won't have much time for that in the following days.

[B4nan]

This should help for v6, if you are just experimenting or starting a new app, I'd suggest you use that right ahead.

ca8795a

I can try to backport it but as mentioned, not sure when I will find time for it, I need to prioritize v6 stable release now. But I do plan at least one more 5.x release backporting things that are simple enough.