sql - Querying an array of objects in JSONB

Question

I have a table with a column of the data type JSONB. Each row in the column has a JSON that looks something like this:

[
  {
    "A":{
      "AA": "something",
      "AB": false
    }
  },
  {
    "B": {
      "BA":[
        {
          "BAAA": [1,2,3,4]
        },
        {
          "BABA": {
           .... 
          }
        }
      ]
    }
  }
]

Note: the JSON is a complete mess of lists and objects, and it has a total of 300 lines. Not my data but I am stuck with it. :(

I am using postgresql version 12

How would I write the following queries:

• Return all row that has the value of AB set to false.
• Return the values of BAAA is each row.

Answer 1

You can find the AB = false rows with a JSON Path query:

select *
from test
where data @@ '$[*].A.AB == false'

If you don't know where exactly the key AB is located, you can use:

select *
from test
where data @@ '$[*].**.AB == false'

---

To display all elements from the array as rows, you can use:

select id, e.*
from test
  cross join jsonb_array_elements(jsonb_path_query_first(data, '$[*].B.BA.BAAA')) with ordinality as e(item, idx)

I include a column "id" as a placeholder for the primary key column, so that the source of the array element can be determined in the output.

Online example