Operators to get a string or more json in Postgres
There are two operators in Postgres that I have trouble telling apart. The ->
operator and the ->>
operator. They both take a json object on the left and a key on the right, but result in different types.
Given json in postgres that looks like this:
> select '{"a": {"b": 2}}'::jsonb;
{"a": {"b": 2 }}
I can traverse the data structure with the ->
operator because it returns a data structure.
> select '{"a": {"b": 2}}'::jsonb -> 'a' -> 'b';
2
I can get a value as a string from the json with the ->>
operator.
> select '{"a": {"b": 2}}'::jsonb ->> 'a';
{"b": 2 } -- ITS A STRING
But if you use the ->>
operator to get a string, then you can't traverse the data structure with the ->
operator anymore.
> select '{"a": {"b": 2}}'::jsonb ->> 'a' -> 'b';
ERROR: operator does not exist: text -> unkown
Tweet