PostgreSQL JSONB - query condition with variable key names

Question

I have gone through various JSONB tutorials:

• https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/
• https://www.wagonhq.com/sql-tutorial/values-from-nested-json
• http://schinckel.net/2014/05/25/querying-json-in-postgres/
• http://stormatics.com/howto-use-json-functionality-in-postgresql/

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.

Answer 1

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)