CODEWITHSUNDEEP
jsonpostgresqljsonb
gcj
gcj

Reputation: 298

How to query Jsonb data with PostgreSQL?

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

table I am working on

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

after query

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

obtaining all the rows which have resourceId

Upvotes: 0

Views: 826

Answers (1)

Tryph
Tryph

Reputation: 6219

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.

Upvotes: 2

Related Questions