0

I have postgres table (let say my_table) containing JSON field (let say json_field) as:

(1, {"data": [{"val_a":1, "b":"somevalue"}, {"val_a":2, "b":"othervalue"}, ...]}),  
(2, {"data": [{"val_a":22, "b":"somevalue2"}, {"val_a":12, "b":"othervalue2"}, ...]}),  
....  
(n, {"data": [{"val_a":11, "b":"somevaluen"}, {"val_a":122, "b":"othervaluen"}, ...]})

I want to return ARRAY - int[] of val_a values only.

I tried stored procedure (plpgsql), as follows:

declare
    data_array json[];
    vals float8[];
begin
    SELECT json_field->'data' into data_array FROM my_table;
    FOREACH i IN ARRAY data_array
        vals := array_append(vals, i->'val_a')
        -- nor array_append(vals, i->'val_a') works
    END LOOP;
    return vals;
end;

which gives me syntax error:
array_append(vals, i->'val_a').

What is the best way to extract val_a?

Using PostgreSQL 12.6

prze gee
  • 61
  • 4
  • 1
    Don't abuse JSON like this. Store these data in a separate table without JSON columns and have a foreign key relationship between the tables. Then the exercise becomes trivial. – Laurenz Albe Mar 09 '21 at 10:37
  • Thank @LaurenzAlbe, unfortunetely can not do thak - I'm working on existing database. – prze gee Mar 09 '21 at 10:49

1 Answers1

0

Use a JSONPATH expression:

SELECT jsonb_path_query_array(json_field, '$.data[*].val_a') FROM my_table;

 jsonb_path_query_array 
------------------------
 [1, 2]
(1 row)

This is available from PostgreSQL v12 on.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263