Anjan Anjan - 4 months ago 28
JSON Question

PostgreSQL JSONB - query condition with variable key names

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.

Answer

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)