Extracting values from Postgres json blob

Lets get value 'a':

select '{"a": "b"}'::json->>'a';
  ?column?  
------------
 b

Howbout a nested value 'b'?

select ('{"a": {"b": "c"}}'::json->>'a')::json->>'b';
 ?column? 
----------
 c

Howbout the first value of an array?

select ('{"a": [{"b": "c"}]}'::json->>'a')::json->>0;
  ?column?  
------------
 {"b": "c"}

There's a better way to get a nested path

select json_extract_path('{"a": {"b": "c"}}', 'a', 'b');
 json_extract_path 
-------------------
 "c"
(1 row)

But you can't grab a value from an array with that method. But at least json_extract_path returns a value of type json so you don't have to cast it before using the feather (->>) operator.

select json_extract_path('{"a": {"b": ["c"]}}', 'a', 'b')->>0;
 json_extract_path 
-------------------
 "c"
(1 row)

Today I Learned

A Hashrocket project Follow on Twitter

Extracting values from Postgres json blob