CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
cjm2671
cjm2671

Reputation: 19456

ERROR: operator does not exist: jsonb[] -> integer

select id,rules from links where id=2;
 id |                                                                                   rules                                                                                   
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  2 | {"{\"id\": \"61979e81-823b-419b-a577-e2acb34a2f40\", \"url\": \"https://www.wikijob.co.uk/about-us\", \"what\": \"country\", \"matches\": \"GB\", \"percentage\": null}"}

I'm trying to get the elements of the jsonb using the operators here https://www.postgresql.org/docs/9.6/functions-json.html

Whether I use 'url', or an integer as below, I get a similar result. 

select id,rules->1 from links where id=2;
ERROR:  operator does not exist: jsonb[] -> integer
LINE 1: select id,rules->1 from links where id=2;
                       ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

What am I doing wrong?

PS Postgres version 9.6.12.

Upvotes: 6

Views: 9135

Answers (1)

klin
klin

Reputation: 121474

The column is an array, you can access the first element using index:

select id, rules[1]
from links
where id = 2

Be sure to check also this answer.

Use jsonb_each() in a lateral join to see all rules in separate rows:

select id, key, value
from links
cross join jsonb_each(rules[1]) as rule(key, value)
where id = 2

You can get a single rule in this way: 

select id, value as url
from links
cross join jsonb_each(rules[1]) as rule(key, value)
where id = 2 and key = 'url'

Use unnest() to find an url in all elements of the array, e.g.:

select id, unnest(rules)->'url' as url
from links
where id = 2;

Upvotes: 2

Related Questions