CODEWITHSUNDEEP
jsonpostgresql
Alexei
Alexei

Reputation: 15674

Full text search in concrete node in json

I has table "Product" with two columns:

  1. Id - Bigint primary key
  2. data - Jsonb

Here example of json:

{
  "availability": [
    {
      "qty": 10,
      "price": 42511,
      "store": {
        "name": "my_best_store",
        "hours": null,
        "title": {
          "en": null
        },
        "coords": null,
        "address": null,

I insert json to column "data".

Here sql get find "my_best_store"

select *
from product
where to_tsvector(product.data) @@ to_tsquery('my_best_store')

Nice. It's work fine.

But I need to find "my_best_store" only in section "availability".

I try this but result is empty:

select *
from product
where to_tsvector(product.data) @@ to_tsquery('availability & my_best_store')

Upvotes: 0

Views: 76

Answers (1)

user330315
user330315

Reputation:

Assuming you want to search in the name attribute, you can do the following:

select p.*
from product p
where exists (select *
              from jsonb_array_elements(p.data -> 'availability') as t(item)
              where to_tsvector(t.item -> 'store' ->> 'name') @@ to_tsquery('my_best_store'))

With Postgres 12, you can simplify that to:

select p.*
from product p
where to_tsvector(jsonb_path_query_array(data, '$.availability[*].store.name')) @@ to_tsquery('my_best_store')

Upvotes: 1

Related Questions