2

Postgres 9.5

Given a table with one field of type json, about 700 rows, and where each row has about 4,000 elements in a single array...

my_db_field:

[0.44577, 0.4855, 0.45429, 0.54437,...]
[0.45012, 0.48698, 0.45715, 0.55337,...]
[0.47347, 0.49156, 0.46079, 0.56818,...]
[0.4936, 0.49835, 0.46086, 0.58195,...]
[0.51068, 0.50511, 0.46228, 0.59482,...]

The PostgreSQL docs show how you can query for a single element inside an array.

 SELECT my_db_field->2 AS test FROM my_db_table

results:

test (of type json)
--------------------
0.4855
0.48698
0.49156
etc.

What I would like to do, though, is select multiple elements in the array and return that as an array in the same format the rows are. By multiple, I mean around 300 elements in the array; e.g., from element 0 to element 300. Does Postgres have a nice syntax for such a query?

  • I would look at restructuring the schema here. – Evan Carroll Feb 9 '17 at 17:45
  • @EvanCarroll - anything in particular you had in mind in that regard? – mg1075 Feb 9 '17 at 17:49
  • 1
    We don't know enough about your schema and the json field do to it, but essentially create a new table test_id int, idx int, elem text. prune the json-array out of the json object and move it to the new table. Then you could just do SELECT * FROM test JOIN test_array USING ( test_id ) WHERE idx < 300. JSON is fine for storing, but I'm questioning whether or not you've outgrown its utility at this point. – Evan Carroll Feb 9 '17 at 17:52
  • I've made my comment an answer if you decide to go down that route. – Evan Carroll Feb 9 '17 at 17:59
2
select  array_to_json 
        (
           (select  array_agg(my_db_field->n order by n) 
            from    generate_series(0,least(300,json_array_length(my_db_field))-1) gs(n) 
            )
        )

from    my_db_table

This solution (the original in this answer) most likely does not guarantee the order of the elements

select (array(select json_array_elements(my_db_field)))[1:300]
from    my_db_table
  • Please check........ – David דודו Markovitz Feb 9 '17 at 16:48
  • Please handle with care, also I assume the elements order will be preserved, I'm afraid it is not guaranteed. I'll have to check it later on. – David דודו Markovitz Feb 9 '17 at 17:10
  • Cool! The only issue is this returns each row as an object instead of an array. Seems hacky, but by adding a json_build_array function, then the result for each row is an array. SELECT json_build_array((array(select json_array_elements(my_db_field)))[1:300])->0 FROM my_db_table – mg1075 Feb 9 '17 at 17:11
  • re: elements order not preserved - hmmm, that would be a concern, although I don't see any dis-ordering right now of return rows or of values inside the arrays. – mg1075 Feb 9 '17 at 17:14
  • 1
    @EvanCarroll - It seems reasonable that the internal implementation of json_array_elements and array will treat elements by their order but their is no order clause in this solution and as far as I know there is no guarantee in the documentation for such behaviour. It doesn't different in concept from inserting rows to a table and expecting that a select will return the rows in the same order they were inserted. – David דודו Markovitz Feb 9 '17 at 18:03
0

We don't know enough about your schema and the json field do to it, but essentially create a new table

CREATE TABLE test_array (
  test_id int       REFERENCES test
  idx     smallint,
  elem    text
  PRIMARY KEY (test_id, idx)
);

prune the json-array out of the json object and move it to the new table. Then you could just do

SELECT * FROM test
JOIN test_array
  USING ( test_id )
WHERE idx < 300

JSON is fine for storing, but I'm questioning whether or not you've outgrown its utility at this point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.