CODEWITHSUNDEEP
postgresql
Sidhha
Sidhha

Reputation: 268

Postgres with json column

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?

Upvotes: 1

Views: 720

Answers (2)

Patrick
Patrick

Reputation: 32161

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.

Upvotes: 1

Schwern
Schwern

Reputation: 164639

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.

Upvotes: 1

Related Questions