Today I Learned

hashrocket A Hashrocket project

Extracting Nested JSON Data

If you are storing nested JSON data in a postgres JSON column, you are likely going to find yourself in a situation where you need to access some of those nested values in your database code. For instance, you may need to get at the license number in this JSON column

'{ "name": "Jason Borne", "license": { "number": "T1234F5G6", "state": "MA" } }'

Unfortunately, the -> operator isn't going to do the trick. You need the json_extract_path function

> select json_extract_path(owner, 'license', 'number') from some_table;


Read more about JSON Functions and Operators.

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.