Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get one element from jsonb array in PostgreSQL

Tags:

sql

postgresql

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

like image 991
Mateusz Urbański Avatar asked Dec 04 '18 18:12

Mateusz Urbański


People also ask

How do I query Jsonb data in PostgreSQL?

Querying the JSON document PostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.

Can you index Jsonb Postgres?

JSONB and Indexes PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.

Can Jsonb be an array?

jsonb[] is not an "extra" datatype, it's simply an array of JSONB values. Similar to text[] or integer[] . You can create arrays from every type.

What is -> in PostgreSQL?

PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.


1 Answers

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.

like image 164
NotAnAuthor Avatar answered Oct 05 '22 10:10

NotAnAuthor