1

I wrote the following query on a local dev environment not realizing that the postgres version on my local dev was 9.3+ but only 9.1 on production. I am hesitant to upgrade the production postgres. Is there any way to write the following query that is compatible with 9.1? I believe the row_to_json is the problem. Is there a 9.1 equivalent? The schema is SalesOrders has many SalesOrderItems. Each SalesOrderItem has many mdns. The below returns me a nice compact nested JSON object when run on 9.3.

select row_to_json(t)   
  from (select s.*,         
    ( select array_to_json(array_agg(row_to_json(d)))         
      from ( select soi.* , (select array_to_json(array_agg(row_to_json(e))) 
         from( select m.* FROM "Mdns" m where m."SalesOrderItemId" = soi.id) e) mdns            
           from "SalesOrderItems" soi where soi."SalesOrderId" =s.id ) d               
            ) as order_items      
   from "SalesOrders" s       where s.order_id = 'SO-83562-0185') t
user1175817
  • 449
  • 2
  • 7
  • 17
  • I'm sorry but json and relative functions are in postgresql from 9.2: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2 – user_0 Dec 29 '14 at 22:24
  • is it possible to write those functions? Is the code for them listed somewhere? – user1175817 Dec 29 '14 at 22:33
  • Btw, the `row_to_json` in `array_to_json(array_agg(row_to_json(d)))` is redundant. It does what you want without that call: `array_to_json(array_agg(d))`. – jpmc26 Dec 29 '14 at 23:03

1 Answers1

3

There's a backport of 9.2's json functions to 9.1 available as an extension called json91.

In general, though, it's a bad idea to develop against a newer version of the DB than you run in production. You'll keep having issues like this.

If you want to use json features you'll want to plan an upgrade to 9.4; PostgreSQL's json support continues to improve with each release.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778