current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

I have gone through various JSONB tutorials:

Consider the following example.

There is a table called plans. It has the following columns:

  1. id (integer, auto-incrementing primary key).
  2. name (string).
  3. structure (jsonb).

The structure column has a regular JSON object having the following structure:

{
  "some_unique_id": {
    "key1": "valueA",   // Fixed key name.
    "key2": "valueB"    // Fixed key name.
  },
  "another_unique_id": {
    "key1": "valueC",   // Fixed key name.
    "key2": "valueB"    // Fixed key name.
  },
  ...                   // can go on up to a 1000 items.
}

Note: The outermost keys are dynamic. They change for every item. The values are just regular JSON objects. Nothing special.

I use UUIDs as the keys in the structure so it is easy to lookup and retrieve a specific value, if I know its UUID.

The other option is to make my structure an array of objects (and put the UUID as a value inside every object) like the following:

[
  {
    "uuid": "some_unique_id",
    "key1": "valueA",   // Fixed key name.
    "key2": "valueB"    // Fixed key name.
  },
  {
    "uuid": "another_unique_id",
    "key1": "valueC",   // Fixed key name.
    "key2": "valueB"    // Fixed key name.
  },
  ...                   // can go on up to a 1000 items.
]

In this latter approach, to retrieve a particular object using its UUID, I would have to loop through the entire array and match the uuid key of every object.

So, I chose the first approach.

The table has 3 records. For this question, the value of the id and name columns are not important.

The actual values of the structure column in the 3 records are as below.

Record 1:

{
  "bab6246d-802c-4b80-af41-ab15fd1541b4": {
    "name": "Sanskrit",
    "children_uuids": [
      "fa42b4b2-a958-42f1-af33-314e8e1fb6a6",
      "3aeeadfe-6ad4-4229-85a5-5de030c08014"
    ],
    "is_invisible_node": true,
    "tags": [
      "paper",
      "course_paper"
    ],
    "type": "course_paper"
  },
  "dbc33473-8453-4cf9-8ecf-d8013283b0d8": {
    "name": "French",
    "children_uuids": [
      "4bf65ff9-3b11-42d5-a744-adcd1fd5a953"
    ],
    "is_invisible_node": true,
    "tags": [
      "paper",
      "course_paper"
    ],
    "type": "course_paper"
  }
}

Record 2:

{
  "ed6164d0-fdc0-4259-90a5-fd60d9d716dc": {
    "name": "Pen and Paper Assessment 1",
    "children_uuids": [

    ],
    "is_invisible_node": false,
    "tags": [
      "paper",
      "assessment"
    ],
    "type": "assessment"
  },
  "059d0116-bca2-49f1-b333-58c4dbec8566": {
    "name": "Content",
    "children_uuids": [

    ],
    "is_invisible_node": false,
    "tags": [
      "paper",
      "assessment"
    ],
    "type": "assessment"
  }
}

Record 3:

{
  "63619c7f-fa73-49af-9df5-4be1eb38cee5": {
    "name": "Q12",
    "children_uuids": [

    ],
    "is_invisible_node": true,
    "tags": [
      "paper",
      "regular_paper"
    ],
    "type": "regular_paper"
  },
  "56eed164-17f7-48e9-b3ce-b5b469e8cb0e": {
    "name": "Q13",
     "children_uuids": [

    ],
    "is_invisible_node": false,
    "tags": [
      "paper",
      "regular_paper"
    ],
    "type": "regular_paper"
  },
  "69d202c1-5c23-412f-860d-1a5d705c31b3": {
    "name": "Q14",
    "children_uuids": [

    ],
    "is_invisible_node": false,
    "tags": [
      "paper",
      "regular_paper"
    ],
    "type": "regular_paper"
  }
}

Now, how do I write queries to do the following two things?

  • I want to get all records which contain any objects with the is_invisible_node property set to true.
  • I want to get all objects which contain regular_paper as one of its tags.

Thank you for reading this far! Any help would be appreciated.

share|improve this question
up vote 1 down vote accepted

I want to get all records which contain any objects with the is_invisible_node property set to true.

Use jsonb_each() to retrieve objects on the second level:

select id, uuid.key uuid
from 
    plans, 
    jsonb_each(structure) uuid
where (value->>'is_invisible_node')::boolean;

 id |                 uuid                 
----+--------------------------------------
  1 | bab6246d-802c-4b80-af41-ab15fd1541b4
  1 | dbc33473-8453-4cf9-8ecf-d8013283b0d8
  3 | 63619c7f-fa73-49af-9df5-4be1eb38cee5
(3 rows)

or

select distinct id
from 
    plans, 
    jsonb_each(structure) uuid
where (value->>'is_invisible_node')::boolean;

 id 
----
  1
  3
(2 rows)

I want to get all objects which contain regular_paper as one of its tags.

The json object tags is an array, so unnest it with jsonb_array_elements_text():

select uuid.key uuid
from 
    plans, 
    jsonb_each(structure) uuid,
    jsonb_array_elements_text(value->'tags') tag
where tag = 'regular_paper';

                 uuid                 
--------------------------------------
 56eed164-17f7-48e9-b3ce-b5b469e8cb0e
 63619c7f-fa73-49af-9df5-4be1eb38cee5
 69d202c1-5c23-412f-860d-1a5d705c31b3
(3 rows)
share|improve this answer
    
Brilliant! Thanks. – Anjan Jul 13 at 10:25
    
Just confirming - will these queries use the jsonb_path_ops GIN index on the structure column? Or does it need the "regular" GIN index? (reference: blog.2ndquadrant.com/jsonb-type-performance-postgresql-9-4) – Anjan Jul 13 at 10:33
    
@Anjan no, functions like jsonb_each or jsonb_array_elements[_text] (set returning json functions) cannot use indexes at all. To use indexes, you need to normalize your db structure. – pozs Jul 13 at 11:13
    
No, the index will not be used as it supports only @> operator, so it would be helpful e.g. in searching a uuid. There is no indeces which could help when using decomposing functions like jsonb_each(). – klin Jul 13 at 11:13
    
That's a shame. A full table scan for such frequent queries would be wasteful. So, if I use my second approach to store my structure (array of objects), is it possible to have queries for both my questions, which would use the index (even regular GIN index, not necessarily jsonb_path_ops)? What would those queries be? I am guessing that for my second question, it just won't be possible. – Anjan Jul 13 at 14:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.