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:
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
| -> | int | Get JSON array element | ‘[1,2,3]’::json->2 |
| -> | text | Get JSON object field | ‘{“a”:1,”b”:2}’::json->’b’ |
| ->> | int | Get JSON array element as text | ‘[1,2,3]’::json->>2 |
| ->> | text | Get JSON object field as text | ‘{“a”:1,”b”:2}’::json->>’b’ |
| #> | array of text | Get JSON object at specified path | ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>'{a,2}’ |
| #>> | array of text | Get JSON object at specified path as text | ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>>'{a,2}’ |
| Function | Return Type | Description | Example | Example Result |
|---|---|---|---|---|
| array_to_json(anyarray [, pretty_bool]) | json | Returns 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]) | json | Returns 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) | json | Returns 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) | int | Returns 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 json | Expands 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 text | Expands 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[]) | json | Returns 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[]) | text | Returns 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 text | Returns 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] | anyelement | Expands 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 anyelement | Expands 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 json | Expands 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
