Extract JSON string and cast to decimal

json, postgresql, postgresql-9.3

Solution

Here is my solution:

SELECT (foo #>> '{path,to,decimal}')::numeric
FROM (
    SELECT '{"path": {"to": {"decimal": "123.45"}}}'::json AS foo
) AS footable

Notice the `#>>` operator to retrieve the value as `text` (and avoid any weird implicit casting).

Here is the SQLFiddle.

Problem

I have a decimal value stored as a string in a JSON column: ``` {"path": {"to": {"decimal": "123.45"}}} ``` I can extract the decimal value using the `#>` operator: ``` SELECT foo #> '{path,to,decimal}' FROM ( SELECT '{"path": {"to": {"decimal": "123.45"}}}'::json AS foo ) AS footable ``` But the result is a json type: ``` "123.45" ``` If I cast it to numeric: ``` SELECT (foo #> '{path,to,decimal}')::numeric FROM ( SELECT '{"path": {"to": {"decimal": "123.45"}}}'::json AS foo ) AS footable ``` I get the error: ``` cannot cast type json to numeric ``` If I cast it to text and then numeric: ``` SELECT (foo #> '{path,to,decimal}')::text::numeric FROM ( SELECT '{"path": {"to": {"decimal": "123.45"}}}'::json AS foo ) AS footable ``` The text string gets quoted which fails to cast to numeric: ``` invalid input syntax for type numeric: ""123.45"" ``` What is the proper way to extract a value from a json structure, and get the value as a non-json type?

Original source