More examples how to work with JSON columns in postgres #1261
Comments
|
there is no special treatment, so better to read the postgres docs :] if you want to join by anything else than a PK of entity, you will need to use QB (or even raw query). here is how you can query by custom sql fragment (so possibly a json expression): https://mikro-orm.io/docs/entity-manager#using-custom-sql-fragments you could also use https://mikro-orm.io/docs/defining-entities/#formulas only thing that uses json columns under the hood are embeddables in object mode: https://mikro-orm.io/docs/embeddables/#storing-embeddables-as-objects if you have something specific on your mind, feel free to ask |
|
I tried native json operator with postgress driver got some error test('Test JSON', async (t) => {
const app = await build(t);
const r = await app.ORM.em.find(TestEntity, {
[expr('(column1->>"verified")::boolean')]: true
});
console.log({ r });
});
do mikro-orm support native json operator ? or how can i do that ? |
|
I see, this is not working as expected as the purpose was mainly to allow using SQL functions. As a workaround, you could do this: const r = await app.ORM.em.find(TestEntity, {
[expr('jsonb_extract_path_text(column1, \'verified\')::boolean')]: true,
}); |
|
What if the field has an index? Using |
|
Custom expressions should be fixed in Few examples from the test case: const r = await orm.em.find(User, {
[expr('(address4->>\'street\')::text != \'\'')]: [],
[expr('lower((address4->>\'city\')::text) = ?')]: ['prague'],
[expr('(address4->>?)::text = ?')]: ['city', 'Prague'],
[expr('(address4->>?)::text')]: ['postalCode', '12000'],
});
expect(mock.mock.calls[0][0]).toMatch('select "e0".* ' +
'from "user" as "e0" ' +
'where (address4->>\'street\')::text != \'\' and ' +
'lower((address4->>\'city\')::text) = \'prague\' and ' +
'(address4->>\'city\')::text = \'Prague\' and ' +
'(address4->>\'postalCode\')::text = \'12000\''); |
We can query by JSON object properties easily:
```ts
const b = await em.findOne(Book, {
meta: {
valid: true,
nested: {
foo: '123',
bar: 321,
deep: {
baz: 59,
qux: false,
},
},
},
});
```
Will produce following query (in postgres):
```sql
select "e0".*
from "book" as "e0"
where ("meta"->>'valid')::bool = true
and "meta"->'nested'->>'foo' = '123'
and ("meta"->'nested'->>'bar')::float8 = 321
and ("meta"->'nested'->'deep'->>'baz')::float8 = 59
and ("meta"->'nested'->'deep'->>'qux')::bool = false
limit 1
```
> All drivers are currently supported (including sqlite and mongo). In postgres we
> also try to cast the value if we detect number or boolean on the right-hand side.
Closes #1359
Related: #1261
|
Check #1384, it will add native support for querying by JSON properties, including type casting in postgres (for number and boolean). |
We can query by JSON object properties easily:
```ts
const b = await em.findOne(Book, {
meta: {
valid: true,
nested: {
foo: '123',
bar: 321,
deep: {
baz: 59,
qux: false,
},
},
},
});
```
Will produce following query (in postgres):
```sql
select "e0".*
from "book" as "e0"
where ("meta"->>'valid')::bool = true
and "meta"->'nested'->>'foo' = '123'
and ("meta"->'nested'->>'bar')::float8 = 321
and ("meta"->'nested'->'deep'->>'baz')::float8 = 59
and ("meta"->'nested'->'deep'->>'qux')::bool = false
limit 1
```
> All drivers are currently supported (including sqlite and mongo). In postgres we
> also try to cast the value if we detect number or boolean on the right-hand side.
Closes #1359
Related: #1261
We can query by JSON object properties easily:
```ts
const b = await em.findOne(Book, {
meta: {
valid: true,
nested: {
foo: '123',
bar: 321,
deep: {
baz: 59,
qux: false,
},
},
},
});
```
Will produce following query (in postgres):
```sql
select "e0".*
from "book" as "e0"
where ("meta"->>'valid')::bool = true
and "meta"->'nested'->>'foo' = '123'
and ("meta"->'nested'->>'bar')::float8 = 321
and ("meta"->'nested'->'deep'->>'baz')::float8 = 59
and ("meta"->'nested'->'deep'->>'qux')::bool = false
limit 1
```
> All drivers are currently supported (including sqlite and mongo). In postgres we
> also try to cast the value if we detect number or boolean on the right-hand side.
Closes #1359
Related: #1261
We can query by JSON object properties easily:
```ts
const b = await em.findOne(Book, {
meta: {
valid: true,
nested: {
foo: '123',
bar: 321,
deep: {
baz: 59,
qux: false,
},
},
},
});
```
Will produce following query (in postgres):
```sql
select "e0".*
from "book" as "e0"
where ("meta"->>'valid')::bool = true
and "meta"->'nested'->>'foo' = '123'
and ("meta"->'nested'->>'bar')::float8 = 321
and ("meta"->'nested'->'deep'->>'baz')::float8 = 59
and ("meta"->'nested'->'deep'->>'qux')::bool = false
limit 1
```
> All drivers are currently supported (including sqlite and mongo). In postgres we
> also try to cast the value if we detect number or boolean on the right-hand side.
Closes #1359
Related: #1261
Is it possible to illustrate more examples how to work with JSON columns in postgres including selects and joins? Thanks
The text was updated successfully, but these errors were encountered: