0

I have objects in postgres db in one column:

{"svc": {"times": [1.2, 1.3, 1.4]}}
{"svc": {"times": [1.6, 2.3, 5.5]}}
...

And with select query:

SELECT services->'svc'->>'times'

I get rows with strings:

"[1.2, 1.3, 1.4]"
"[1.6, 2.3, 5.5]"

or with SELECT json_agg(services->'svc'->>'times'):

["[1.2, 1.3, 1.4]", "[1.6, 2.3, 5.5]"]

But I need an arrays (one array will be good as well):

[1.2, 1.3, 1.4]
[1.6, 2.3, 5.5]

So I can process it with python script (but I want to get array directly from db). i.e.

rows = select_result
my_list = []
for row in rows:
    my_list.extend(row)

Tried lot of pg functions (split, array) for that but nothing woks well. Should I change type of data stored to achieve this by default?

pbaranski
  • 17,946
  • 16
  • 88
  • 101
  • You can try [`ast.literal_eval`](https://stackoverflow.com/a/1894296/4985099) – sushanth Sep 02 '20 at 07:59
  • 1
    Are you having problems with the postgres part or the python part? If you want the times array as a Postgres array - use `->`. `SELECT '{"svc": {"times": [1.2, 1.3, 1.4]}}'::json->'svc'->'times';` – madflow Sep 02 '20 at 08:07
  • @madflow the double `>>` make me confused - I got nice output when using tool for db queries but for code it made me looking for some wierd solution:) Thanks and post the answer - I will upvote! – pbaranski Sep 02 '20 at 08:15

0 Answers0