Select data from a nested jsonb array in Postgres

Question

I want to select the last value from an array nested like this:

{ tier: [
  { tier: [] },
  { tier: [] },
  { tier: [
      { tier: 1},
      { tier: 2},
      { tier: 3}, // < this item       
    ] 
  },
  ] 
}

I've tried to use something like this from other examples, but the syntax is eluding me. I either get a 'column does not exist' or 'cannot extract elements from object'

SELECT *, 
  t0->tier->(jsonb_array_length(t0->tier) - 1) t1,
  t1->tier->(jsonb_array_length(t1->tier) - 1) t2,
  t2->tier->(jsonb_array_length(t2->tier) - 1) t3,
FROM data t0

GMB · Accepted Answer · 2020-05-06 21:49:32Z

3

You can reference it by negative index:

SELECT t -> 'tier' -> -1 -> 'tier' -> -1
FROM data;

The -1 here references the last item of the array, which means you don't need to calculate how many elements there are.

edited May 6, 2020 at 21:49

GMB

224k25 gold badges103 silver badges151 bronze badges

answered May 6, 2020 at 21:38

Thom Brown

2,0698 silver badges14 bronze badges

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

2 Comments

GMB Over a year ago

I suspect that OP wants: js -> 'tier' -> -1 -> 'tier' -> -1 ->> 'tier', which gives the value of the last element rather than the object itself. +1 anyway.

Thom Brown Over a year ago

Ah, that wasn't clear to me. Thanks for adding this clarification.

Collectives™ on Stack Overflow

Select data from a nested jsonb array in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related