Today I Learned

hashrocket A Hashrocket project

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
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.