Question 176729 in PostgreSQL Question posted in
The official documentation can be found here.

How can I query text & nums in Postgres jsonb using indexes?

I have a Postgres table named "base" with a jsonb field called "addresses" which stores multiple addresses for the same user.

Here is the table structure:

create table base(name text, addresses jsonb);

insert into base(name, addresses) values('John', '[{"zip":"01431000","state":"SP","number":100,"street1":"Avenida Brasil","city_name":"São Paulo"},{"zip":"01310900","state":"SP","number":200,"street1":"Avenida Paulista","city_name":"São Paulo"}]');
insert into base(name, addresses) values('Doe', '[{"zip":"01332000","state":"SP","number":250,"street1":"Rua Itapeva","city_name":"São Paulo"}]');

I’m trying to search for specific row containing an address using combination of "street1" and "number" keys using gin index on key "street1".

Here is the structure of the jsonb field:

[
  {
    "zip":"01431000",
    "state":"SP",
    "number":100,
    "street1":"Avenida Brasil",
    "city_name":"São Paulo",
  },
  {
    "zip":"01310900",
    "state":"SP",
    "number":200,
    "street1":"Avenida Paulista",
    "city_name":"São Paulo",
  },
]

How I would be able to search, for example, for a row containing: to_tsquery('simple', 'paulista') and number = 200? I need to be able to search for partial "street1" name.

I have a index for searching by "zip" and "number":

create index base_addresses_idx on base using gin (addresses jsonb_path_ops);

And the search is really fast using this query:
select * from table. where addresses @> '[{"zip":"01431000","number":100}]';

But I am not able to query for partial "street1" name (using tsvector) and a specific number.

PS: I am using PostgreSQL 15.1

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution I found to this problem was to create a materialized view with the expanded addresses field:

    create materialized view address as (
  select
    base_id,
    address,
    to_tsvector('simple', concat_ws(' ', jsonb_extract_path_text(address, 'street1'), jsonb_extract_path_text(address, 'number'))) as tsvector_address
  from (
    select
      base.id,
      jsonb_array_elements(base.addresses) as address
    from base
  ) as address
);

    Then, I have created those indexes:

    create index address_id_idx on address using btree (base_id);
create index address_address_idx on address using gin (tsvector_address);

    This way, I can query easily with an inner join between base and address using tsvector on street1 and number keys:

    select distinct on (base.id) base.*
from base

inner join address
on base.id = address.base_id
where address.tsvector_address @@ to_tsquery('simple', 'query')

  2. 0

    Subqueries should be able to do this. For each row in base, use jsonb_array_elements() to locate all dictionaries containing a value in street1 like your substring. Then from those, find any containing your other condition. Put this in a WHERE EXISTS clause by selecting 1 at each occurrence.

    SELECT *
FROM base
WHERE EXISTS (
  SELECT 1
  FROM (
    SELECT addr
    FROM jsonb_array_elements(addresses) AS addr
    WHERE addr->>'street1' ILIKE '%Paulista%'
  ) AS filtered
  WHERE filtered.addr->>'number' = '200'
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search