Postgres with json column

Question

I have a column in Postgres database which has a json

{"predict":[{"method":"A","val":1.2},{"method":"B","val":1.7}]}

I would like to extract both val as a separate column. Is there a way I could do this from within Postgres?

Answer 1

Postgres introduced JSON types plus functions and operators in 9.2. If your column is a JSON type you can use them to do your extraction.

Answer 2

If the json always has the structure you indicate (i.e. a key "predict" that holds an array with two JSON objects each having a "method" and a "val" key) then the solution is simply:

SELECT ((my_json->'predict')->>0)->'val' AS method_a,
 ((my_json->'predict')->>1)->'val' AS method_b
FROM my_table;

If the structure can vary then you'd have to tell us more about it to provide you with a solution.

• Sidhha: The column is text so I am casting it as JSON and then I keep getting this error ERROR: operator does not exist: json -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
• Patrick: Can you please put the exact command that you use so I can see how exactly you use the cast?