I have a json document for each row in my table (PostgreSQL 10) with the following structure

"partyboat": {
    "participants": [{
        "name": "Bill Lambeer",
        "role": "Lessee"
    }, {
        "name": "Ted Dansonite",
        "role": "Lessor"
    }, {
        "name": "EMILY With a Y",
        "role": "Lessor"

I need to find rows using criteria like "Lessee starts with Bill and Lessor contains Dansonite" or "Has participant name that starts with Emily"

I have a working function that I can use but it takes 2 minutes on 2 million records. To slow for my use.

Here is my working function and an example of how I use it.

create function test_hasparticipant(jsondata jsonb, role varchar, name    varchar)
returns bool
language sql
select exists(
       with participantarrays as
         (select jsondata
          where jsonb_typeof(jsondata -> 'partyboat' -> 'participants')    = 'array')
         , participants_flattened as
         (select jsondata, jsonb_array_elements(jsondata -> 'partyboat' -> 'participants') participants
          from participantarrays)
         select *
         from participants_flattened
         where participants -> 'role' ? role
           and participants ->> 'name' like name)

select *
from partyboats
where hasparticipant(jsondata, 'Lessee', 'Bill%') and hasparticipant  (jsondata, 'Lessor', '&Dansonite%')

I have been trying some indexing but nothing makes much of an impact.

create index participantnames on partyboats((jsondata->'partyboat'->'participants'->>'names'));
create index participantgroup on partyboats using gin((jsondata->'partyboat'->'participants'));
create index particpantrole on partyboats using gin((jsondata->'partyboat'->'participants'->'role'));
create index particpanttypeof on partyboats((jsonb_typeof(jsondata->'partyboat'->'participants')));

I am open to reformatting my json document if there is a model that is more efficient to query. The need for the typeof query will be removed eventually but from what I can tell it is not impacting query time very much.

How can I query these array objects efficiently? Is querying arrays in the json always going to be slow if I require wildcard support?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.