Selecting values inside array in jsonb

Question

I have the following table

CREATE TABLE country (
    id      INTEGER NOT NULL PRIMARY KEY ,
    name        VARCHAR(50),
    extra_info  JSONB
);
 
INSERT INTO country(id,extra_info)
VALUES (1, '{ "name" : "France", "population" : "65000000", "flag_colours": ["red", "blue","white"]}');
 
INSERT INTO country(id,extra_info)
VALUES (2, '{ "name": "Spain", "population" : "47000000", "borders": ["Portugal", "France"] }');

SELECT extra_info->>'name' as Name, extra_info->>'population' as Population
FROM country

I would like to select id and extra info

SELECT id,extra_info->>'population' as Population,extra_info->'flag_colours'->>1 as colors
FROM country

This query shows only id,population but the flag_colors is null.

I also would like to use flag_colors in a condition

SELECT extra_info->>'population' as Population FROM country where extra_info->'flag_colours'->>0

i get this error

ERROR:  argument of WHERE must be type boolean, not type text
LINE 1: ...o->>'population' as Population FROM country where extra_info...
                                                             ^
SQL state: 42804
Character: 67

How can i fix the two queries?

Misspelling: flag_colours in the data vs flag_colors in the query — Bergi
– Bergi, Commented Jul 12, 2020 at 11:30
"I also would like to use flag_colors in a condition" - what should the condition filter for? For flag_colors to exist in the json? For the first flag color to be red? Or something else? — Bergi
– Bergi, Commented Jul 12, 2020 at 11:32

Gandalf · Accepted Answer · 2020-07-12 12:37:41Z

1

Wrote my query this way

SELECT *
FROM country
WHERE (extra_info -> 'flag_colours') ? 'red' and (extra_info -> 'flag_colours') ? 'white'

Many thanks to alt-f4

updated answer https://stackoverflow.com/a/62858683/492293

answered Jul 12, 2020 at 12:37

Gandalf

13.7k34 gold badges106 silver badges180 bronze badges

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

Collectives™ on Stack Overflow

Selecting values inside array in jsonb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related