Support for PostgreSQL column expressions (JSON/hstore dereferencing)

[orospakr]

PostgreSQL supports the usage of special expressions (->>, ->, =>, etc.) in lieu of column names in order to dereference a subfield. It would be awesome if AREL (and ultimately AR) supported them.

I apologise in advance for any naive assumptions or terminology I make here. I don't even know if PostgreSQL's use of such custom operators is a syntax consistent with standard SQL:2011.

http://www.postgresql.org/docs/9.3/static/functions-json.html

This has some pretty powerful implications: you can discriminate with and even join using the values inside hstore and json columns, just the same as if they were full-blown columns.

Examples:

Discriminating a select with a condition that specifies a given value for a JSON field:

SELECT * FROM "kases" WHERE (my_inline_json->>'myfield' = 'bender');

Using a foreign key value in a table that refers to "sub-column" inside a JSON column on another table with a simple inner join:

SELECT * FROM "kases" INNER JOIN "robots" ON robots.myfield_fk = cases.my_inline_json->>'myfield';

Postgres' query planner will smartly use indexes on these JSON elements if you've created them, just as it would do for actual standard relational columns.

This has pretty profound applications for ActiveRecord, because it could enable the use of conditions and relations on json/jsonb/hstore fields' "subfields" (as currently used with store_accessor).

Right now, the column quoting logic of AREL appears to preclude their use.

[orospakr]

As a tiny start, I was able to do it with a SqlLiteral:

table = Arel::Table.new(:kases)
op = Arel::SqlLiteral.new("contents->>'myfield'")
query = table.where(op.eq("bender"))

Produces:

SELECT FROM "kases"  WHERE contents->>'myfield' = 'bender'

Of course, I'd assume that this means none of Arel's relational algebraic evaluation/simplification can occur.

[chochkov]

you can also do this using Arel::Nodes::InfixOperation, if it works better for you:

# Arel::SqlLiteral.new("contents->>'myfield'")
op = Arel::Nodes::InfixOperation.new('->>', table[:contents], 'myfield')
query = table.where(op.eq('bender'))

[lloeki]

Is this relevant? https://github.com/guyboertje/arel-pg-json

[guyboertje]

Regarding the above lib, I am v nearly done with it - will submit PR soon.

[lloeki]

Good to hear, @guyboertje!

[raszi]

@guyboertje do you have any updates on that gem? we would love to start using it :)

[guyboertje]

@raszi and @lloeki - i just need to add some tests to smooth the PR. There might be something I have missed. I don't know whether it should be merged here or work as a stand alone gem. @rafaelfranca - PR or Gem?

[guyboertje]

No answer from arel team.

@raszi and @lloeki - I have released the gem to rubygems. v0.0.9. Let me know how you are getting on. It is tested with arel 5 and 6

[lloeki]

We're only investigating porting our app for now so don't expect feedback too soon, but this will be one less hurdle. Thanks @guyboertje!

[rhymes]

Any news on this front?

[guyboertje]

@rhymes - about?

[rhymes]

@guyboertje about the ticket opened by @orospakr. Just wanted to know if the ARel team is considering it.

[guyboertje]

@rhymes - I asked. Perhaps my request got lost in the noise. I ended up publishing it as a gem.

[guyboertje]

@rafaelfranca - Please consider including arel-pg-json into Arel. I don't work with Arel/Posgres anymore but I will help to get the library into a mergeable shape if there is any desire to include it.

[EverybodyKurts]

Are there any plans to merge this into arel? If not, is there any desire to have this merged into arel?

[guyboertje]

@KurtRMueller - please bug the Rails team. I don't use Postgres anymore so I have no motivation to follow this up. AFAIAC Arel can have this code. If they say they are interested I will create a PR but if not I can't spare any time to create a PR on speculation that they will merge it.

[jrochkind]

I would love to have a way to use the postgres jsonb merge operator too, particularly in assignment, to generate:

update something
set jsonb_column = jsonb_column || '{"merge": "value"}'
where id = whatever

Hacking away at things starting at the top in Rails, definitely ran up against Arel here, and am hitting some walls.

[matthewd]

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)

[eikes]

The ActiveRecordExtended gem helps a lot when working with jsonb columns:

https://github.com/GeorgeKaraszi/ActiveRecordExtended