Predictive Hacks

How to get the key-value from JSON Objects in Postgres

Assume that the data column is of the form {"name":"George" , "surname":"Pipis"}. You can extract the value of each key as follows.

select data::json->'name' as name_col, data::json->'surname' as surname_col
from my_table 

In case that you have a nested JSON like {"id": {"name": "George", "surname":"Pipis"} and you want to get the name and the surname you should write:

select data::json->'id'->>'name' as name_col, data::json->'id'->>'surname' as surname_col
from my_table 

More examples below:

OperatorRight Operand TypeDescriptionExample
->intGet JSON array element‘[1,2,3]’::json->2
->textGet JSON object field‘{“a”:1,”b”:2}’::json->’b’
->>intGet JSON array element as text‘[1,2,3]’::json->>2
->>textGet JSON object field as text‘{“a”:1,”b”:2}’::json->>’b’
#>array of textGet JSON object at specified path‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>'{a,2}’
#>>array of textGet JSON object at specified path as text‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>>'{a,2}’
FunctionReturn TypeDescriptionExampleExample Result
array_to_json(anyarray [, pretty_bool])jsonReturns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension 1 elements if pretty_bool is true.array_to_json(‘{{1,5},{99,100}}’::int[])[[1,5],[99,100]]
row_to_json(record [, pretty_bool])jsonReturns the row as JSON. Line feeds will be added between level 1 elements if pretty_bool is true.row_to_json(row(1,’foo’)){“f1″:1,”f2″:”foo”}
to_json(anyelement)jsonReturns the value as JSON. If the data type is not built in, and there is a cast from the type to json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that it is legal JSON.to_json(‘Fred said “Hi.”‘::text)“Fred said \”Hi.\””
json_array_length(json)intReturns the number of elements in the outermost JSON array.json_array_length(‘[1,2,3,{“f1″:1,”f2”:[5,6]},4]’)5
json_each(json)SETOF key text, value jsonExpands the outermost JSON object into a set of key/value pairs.select * from json_each(‘{“a”:”foo”, “b”:”bar”}’)key | value —–+——- a | “foo” b | “bar”
json_each_text(from_json json)SETOF key text, value textExpands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.select * from json_each_text(‘{“a”:”foo”, “b”:”bar”}’)key | value —–+——- a | foo b | bar
json_extract_path(from_json json, VARIADIC path_elems text[])jsonReturns JSON object pointed to by path_elems.json_extract_path(‘{“f2”:{“f3″:1},”f4”:{“f5″:99,”f6″:”foo”}}’,’f4′){“f5″:99,”f6″:”foo”}
json_extract_path_text(from_json json, VARIADIC path_elems text[])textReturns JSON object pointed to by path_elems.json_extract_path_text(‘{“f2”:{“f3″:1},”f4”:{“f5″:99,”f6″:”foo”}}’,’f4′, ‘f6’)foo
json_object_keys(json)SETOF textReturns set of keys in the JSON object. Only the “outer” object will be displayed.json_object_keys(‘{“f1″:”abc”,”f2″:{“f3″:”a”, “f4″:”b”}}’)json_object_keys —————— f1 f2
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]anyelementExpands the object in from_json to a row whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value is used.select * from json_populate_record(null::x, ‘{“a”:1,”b”:2}’)a | b —+— 1 | 2
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]SETOF anyelementExpands the outermost set of objects in from_json to a set whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value is used.select * from json_populate_recordset(null::x, ‘[{“a”:1,”b”:2},{“a”:3,”b”:4}]’)a | b —+— 1 | 2 3 | 4
json_array_elements(json)SETOF jsonExpands a JSON array to a set of JSON elements.json_array_elements(‘[1,true, [2,false]]’)value ———– 1 true [2,false]

More Data Science Hacks?

You can follow us on Medium for more Data Science Hacks

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

Join the inner circle

Join our 2.000+ members and receive exclusive
data science content 🧪