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

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

Sample data is better presented as formatted text which can be copied to build up a test-bed. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Aug 21, 2020 at 12:03

Tryph · Accepted Answer · 2020-08-21 12:36:28Z

2

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.

edited Aug 21, 2020 at 12:36

answered Aug 21, 2020 at 12:10

Tryph

6,27935 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query Jsonb data with PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related