1

I'm currently using postgres (version 9.4.4) to save entire json documents of projects in one table like this (simplified):

CREATE TABLE projects (   
  id numeric(19,0) NOT NULL,   
  project bson NOT NULL )

The project json is something like this (OVERLY-simplified):

{
  "projectData": {
    "guid":"project_guid",
    "name":"project_name"
  },
  "types": [
    {
      "class":"window",
      "provider":"glassland"
      "elements":[
        {
            "name":"example_name",
            "location":"2nd floor",
            "guid":"1a",
          },
          {
            "name":"example_name",
            "location":"3rd floor",
            "guid":"2a",
          }
      ]
    },
    {
      "class":"door",
      "provider":"woodland"
      "elements":[
          {
            "name":"example_name",
            "location":"1st floor",
            "guid":"3a",
          },
          {
            "name":"example_name",
            "location":"2nd floor",
            "guid":"4a",
          }
      ]
    }
  ]
}

I've been reading documentation on operators ->, ->>, #>, @> and so on. I did some tests and successful selects. But I can't manage to index properly, specially nested arrays (types and elements).

Those are some example selects I would like to learn how to optimize (there are plenty like this):

select distinct types->'class' as class, types->'provider' as type 
from projects, jsonb_array_elements(project#>'{types}') types;

select types->>'class' as class, 
       types->>'provider' as provider,
       elems->>'name' as name, 
       elems->>'location' as location,
       elems->>'guid' as guid,
from projects, jsonb_array_elements(project#>'{types}') types, jsonb_array_elements(types#>'{elements}') elems
where types->>'class' like 'some_text%' and elems->'guid' <> '""';

Also I have this index:

CREATE INDEX idx_gin ON projects USING GIN (project jsonb_ops);

Both of those selects work, but they don't use te @> operator or any operator that can use the GIN index. I can't create a index btree ( create index idx_btree on tcq.json_test using btree ((obra->>'types')); ) because the size of the value exceeds the limit (for the real json). Also I can't ( or I don't know how to ) create an index for, let's say, guids of elements ( create index idx_btree2 on tcq.json_test using btree((obra->>'types'->>'elements'->>'guid')); ). This produces a syntax error.

I been trying to translate queries to something using @> but things like this:

select count(*)
from projects, jsonb_array_elements(project#>'{types}') types
where types->>'class' = 'window';

select count(*)
from projects
where obra @> '{"types":[{"class":"window"}]}';

produce a different output.

Is there a way to properly index the nested arrays of that json? or to properly select taking advantage of the GIN index?

3
  • Your last 2 example queries count completely different things. Which one would you like? (the first counts how many {"class":"window"} do you have in any types array element in any row of projects, while the second counts how many projects rows have at least one array element of {"class":"window"} in the types array) Commented Mar 21, 2017 at 13:22
  • Yep, that's what I thought. First one was what I wanted. Though those where just some experiments I made to test the @> operators and the index. Commented Mar 21, 2017 at 14:55
  • unfortunately, you cannot speed up queries with jsonb_array_elements (or queries with any SRFs joined with LATERAL -- this is implicit in your query) with any kind of indexes. You can build a special expression index with your own defined IMMUTABLE function to handle a few, special cases though. Commented Mar 21, 2017 at 15:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.