How to query Jsonb data with PostgreSQL?

Question

I am creating some views by working with those data bassically UUID and some concepts, I managed to obtain what I expected from the first level, but I am having problems when trying to obtain second level data. The table I am working within the column tiledata are some nested data which I would like to access, this is the

When I run the query

SELECT tileid, nodegroupid,
   tiledata ->'34cfea8a-c2c0-11ea-9026-02e7594ce0a0' AS ACTOR
   FROM tiles
   WHERE tiledata -> '34cfea8a-c2c0-11ea-9026-02e7594ce0a0' IS NOT NULL;

I obtain this

How Could I obtain for example resourceId from this nested data,

Answer 1

You can use the jsonb_array_elements function to extract each element of a JSONB array as a new JSONB value on which you can use any other JSONB function or operator.

The thing to know is that each array value will generate a new row in the record set.

---

In your case, you can try:

SELECT tileid, nodegroupid,
   jsonb_array_elements(tiledata ->'34cfea8a-c2c0-11ea-9026-02e7594ce0a0')->'resourceID'
   FROM tiles
   WHERE tiledata -> '34cfea8a-c2c0-11ea-9026-02e7594ce0a0' IS NOT NULL;

---

See this fiddle for a simple example.

See the doc for more details.