Extract JSON string and cast to decimal

Question

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?

Answer 1

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.