Get one element from jsonb array in PostgreSQL

Question

I have the following schema in my PostgreSQL database:

CREATE TABLE survey_results (
    id integer NOT NULL
);

CREATE TABLE slide_results (
    id integer NOT NULL,
    survey_result_id integer,
    buttons jsonb DEFAULT '[]'::jsonb
);

INSERT INTO survey_results (id)
  VALUES (1);

INSERT INTO slide_results (id, survey_result_id, buttons)
  VALUES (1, 1, '[{"text": "Not at all"}, {"text": "Yes"}]');

INSERT INTO slide_results (id, survey_result_id, buttons)
  VALUES (2, 1, '[{"text": "No"}, {"text": "Yes"}]');

And the following query:

WITH data AS ( 
  select 
    sr.id ,
    jsonb_agg(row_to_json(slr)) AS json_row
  from slide_results slr
  INNER JOIN survey_results sr ON sr.id = slr.survey_result_id
  group by sr.id
)

SELECT id, json_row->0->>'buttons' from data;

which returns:

| id  | ?column?                                  |
| --- | ----------------------------------------- |
| 1   | [{"text": "Not at all"}, {"text": "Yes"}] |

I want this query to return only the first element of buttons array. I tried something like this:

WITH data AS ( 
  select 
    sr.id ,
    jsonb_agg(row_to_json(slr)) AS json_row
  from slide_results slr
  INNER JOIN survey_results sr ON sr.id = slr.survey_result_id
  group by sr.id
)

SELECT id, json_row->0->>'buttons'->>1 from data;

but this returns me error:

Query Error: error: operator does not exist: text ->> integer

How can I fix that?

https://www.db-fiddle.com/f/gP761psywgmovfdTT7DjP4/1

NotAnAuthor · Accepted Answer · 2018-12-04 18:44:42Z

90

SELECT id, json_row->0->'buttons'->>0 from data;

You are referring to 'buttons' as text instead of an object.

Also an array's index is 0 based so pointing to 1 will yield the 2nd element.

answered Dec 4, 2018 at 18:44

NotAnAuthor

1,3319 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Get one element from jsonb array in 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