How to parse a nested Postgres JSONB[] array?

Question

PostgreSQL

I have a table

CREATE TABLE posts (
    id uuid PRIMARY KEY,
    tags jsonb[]
);

It has content that looks like this


I need to parse the tags column

{"{\"color\": 35, \"number\": 21}","{\"color\": 54, \"number\": 2}"}

Tell me how I can parse such content by SQL query columns, so that it would look something like this.

post_id    |  color           | number 
**         |  54              | 21      
**         |  35              | 2

How should I write a SQL query?

Answer 1

Did you try to read the docs?

Answer 2

select id, t->'color', t->'number' from posts cross join unnest(tags) as t;

It's a rather strange idea to do jsonb[]. Usually they make an array inside json, not an array from json.