Postgres

to_json(anyelement)    

disclaimer: I am terrible at writing SQL

Chase Gilliam

Postgres Supports JSON Serialization at the DB Level

• Some functions in 9.2 +
• Faster than AR queires + to_json
• Easy(ish) to use
• Offers some guarantees/validations 
• SQL is declarative
• Postgres has a query planner

As easy as...

For simple examples you can simply use a nested select, but the benefits similarly small.

select row_to_json(p)
from (
  select 
    name, 
    phone_number, 
    birthdate, 
    hair_color, 
    height, 
    weight 
  from people
) p

But We Can Do Better

Associations are where we see it start to pay off.

select row_to_json(person)
    from (
      select name, phone_number, 
        birthdate, hair_color, 
        height, weight, 
        date_part('year',age(birthdate)) as age,
        (
          select COALESCE(array_to_json(array_agg(row_to_json(a))), '[]')
          from (
            select DISTINCT street, unit, city, state, zip
            from addresses
            where person_id=people.id
          ) a
        ) as addresses
    from  people
    where id = #{id}
    ) as person

pry(main)> Person.json_associations(1)
  Person Load (1.1ms)      select row_to_json(person)
    from (
      select name, phone_number, birthdate, 
        hair_color, height, weight, 
        date_part('year',age(birthdate)) as age,
        (
          select COALESCE(array_to_json(array_agg(row_to_json(a))), '[]')
          from (
            select DISTINCT street, unit, city, state, zip
            from addresses
            where person_id=people.id
          ) a
        ) as addresses
    from  people
    where id = 1
    ) as person

=> {"name"=>"Chase",
 "phone_number"=>"4342509824",
 "birthdate"=>"1987-02-28",
 "hair_color"=>"brown",
 "height"=>72,
 "weight"=>168,
 "age"=>29,
 "addresses"=>
  [{"street"=>"10 West Chase St.", "unit"=>"1B", 
    "city"=>"Baltimore", "state"=>"MD", "zip"=>"21201"}, 
    {"street"=>"Some Street", "unit"=>"2100", 
    "city"=>"Brooklyn", "state"=>"NY", "zip"=>"11217"}]}

Wow 1.1ms!

Alternatively

a = Address.where(person_id: 1).map(&:attributes)
Person.last.attributes.merge(addresses: a)

But that takes twice as long.

And it won't Scale ™!

How About ActiveModelSerializers?

Don't Do This! I mean, maybe if you like.

Serializers are easier to write, but slower.

serializer_options = {}
serializer = SomeSerializer.new(resource, serializer_options)

Nontrivial SQL Examples Are Far Faster!

Comparison:
sql:                           84.7 i/s
active_model_serializer:       7.1 i/s - 11.94x slower

=> #<Benchmark::IPS::Report:0x007f994da66ba0 
@entries=[
#<Benchmark::IPS::Report::Entry:0x007f994dad9e20 
@label="sql", @microseconds=5068335.294723511, 
@iterations=427, @ips=84.73684840784838, @ips_sd=7, 
@measurement_cycle=7, @show_total_time=false>, 
#<Benchmark::IPS::Report::Entry:0x007f9952426768 
@label="active_model_serializer", @microseconds=5097223.043441772, 
@iterations=35, @ips=7.0979351818635905, @ips_sd=2, 
@measurement_cycle=1, @show_total_time=false>], 
@data=nil>

Taken From Production Code

Now That I Have Convinced You!

Some Methods Worth knowing

• coalesce() - handles empty records
• array_to_json() - returns the array as a JSON array
• array_agg() - turns values into an array
• row_to_json() - turns a DB row into json
• json_build_object() - creates JSON from variadic k,v list

DEMOS!

Thanks For Your Time

Chase Gilliam

Avhana Health

@chasegilliam | chasegilliam.io