Select subset of an array in jsonb postgres

Question

I have a table in Postgres 9.6.3:

CREATE TABLE public."Records"
(
    "Id" uuid NOT NULL,
    "Json" jsonb,
    CONSTRAINT "PK_Records" PRIMARY KEY ("Id")
)

Inside my "Json" column i store arrays like so:

[
  {"a":"b0","c":0,"z":true}, 
  {"a":"b1","c":1,"z":false},  
  {"a":"b2","c":2,"z":true}
]

There can be some 10 million entries in each array, and there can be some 5 million records in the table.

I want to get the JSON out, paged, e.g. skip 1 record and return 2 records.

I can do it like so:

select string_agg(txt, ',') as x FROM 
(select jsonb_array_elements_text("Json") as txt 
FROM "Records" where "Id" = 'de70aadc-70e8-4c77-bd4b-af75ed36897e' -- some id here
limit 50 offset 5000 -- paging parameters
) s;

However, the query takes almost a second (between 780 and 900 msec) to run on my dev laptop with some quite decent hardware (macbook pro 2017). Note: the timing is for the data sizes specified above, of course the sample data of 3 records returns faster.

Adding a GIN index like so: CREATE INDEX records_gin ON "Records" USING gin ("Json"); didn't actually do anything for the query performance (i suppose because i am not querying by the contents of the array).

Is there any way to make this work faster?

If you always need a subset of the array, this might be a rare case of using an jsonb[] array. In that case you can use the native array syntax: some_col[2:10] to select a part of the array — user330315
– user330315, Commented Dec 22, 2017 at 9:08
@a_horse_with_no_name that doesn't work so well because that requires me to lose JSON syntax in favor of the native postgresql array syntax {'JSON HERE','JSON HERE}. — zaitsman
– zaitsman, Commented Dec 28, 2017 at 2:48

Laurenz Albe · Accepted Answer · 2017-12-22 09:05:05Z

1

It would be faster if you normalized your data and stored the array elements in a second table. Then you could use keyset pagination to page through the data.

answered Dec 22, 2017 at 9:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

zaitsman Over a year ago

That would impede my other use case of selecting ALL the data in one go as a string

Laurenz Albe Over a year ago

Would it slow you down considerably? You can either optimize a data model for analytic queries that select everything (denormalize) or for queries that select only a few values.

zaitsman Over a year ago

Well at 10 million rows in the new table * 5 million records in the first table i am somewhat worried about performance. I will try to load test that.

Laurenz Albe Over a year ago

You could partition the table if it grows too large, ideally along the id column (batch several ids in one partition to keep the number of partitions reasonable). Don't you run OOM handling a 5 million entry JSONB?

zaitsman Over a year ago

well one entry of 10 million small rows is just a few hundred megs when already serialized? I never download all 10 mils of all 5 mils ;)

|

Collectives™ on Stack Overflow

Select subset of an array in jsonb postgres

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related