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?

Schwern · Accepted Answer · 2016-04-17 04:06:00Z

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.

answered Apr 17, 2016 at 4:06

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick · Accepted Answer · 2016-04-17 10:38:18Z

1

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.

answered Apr 17, 2016 at 10:38

Patrick

33k7 gold badges73 silver badges102 bronze badges

2 Comments

Sidhha Over a year ago

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 Over a year ago

Can you please put the exact command that you use so I can see how exactly you use the cast?

Collectives™ on Stack Overflow

Postgres with json column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related