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?

p4x
  • 384
  • 1
  • 5
  • 16
  • 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) – pozs Mar 21 '17 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. – p4x Mar 21 '17 at 14:55
  • unfortunately, you cannot speed up queries with `jsonb_array_elements` (or queries with any [SRFs](https://www.postgresql.org/docs/current/static/functions-srf.html) 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](http://stackoverflow.com/q/28486192/1499698) to handle a few, special cases though. – pozs Mar 21 '17 at 15:25

0 Answers0