jsonb-notes.md

I learned a little bit about building queries (even with Rails/ARel) of postgres JSONB columns in my side project that I wanted to share.

I have a model that represents a Japanese word:

{"id"=>4111,
 "type"=>"vocabulary",
 "characters"=>"洗う",
 "meanings"=>[{
    "meaning"=>"To Wash", 
    "primary"=>true, 
    "accepted_answer"=>true
 }],
 "readings"=>[{"primary"=>true, "reading"=>"あらう", "accepted_answer"=>true}],
 "parts_of_speech"=>["transitive_verb", "godan_verb"]}

And meanings and readings are JSONB columns:

    create_table :items do |t|
      t.string :type, :null => false
      t.string :characters, :null => false
      t.jsonb :meanings, :null => false
      t.jsonb :readings
      t.string :parts_of_speech, :array => true
    end 

If I query the JSON object with @>, then I basically can specify any mirroring JSON I want on the righthand side and if it matches then it'll return (and this is indexible with something like but not exactly CREATE INDEX meanings_meaning_on_items ON users USING GIN ((meanings->'meaning')))

Item.where("meanings @> ?",[{:meaning => 'To Wash'}].to_json)

Will actually find records like the above! However, what I really wanted to do was a LIKE query against a deeply nested property, as if it was coming in from a search engine. So I started playing around here https://www.postgresql.org/docs/9.4/static/functions-json.html

    Item.select('items.*, meaning_texts.meaning as meaning_text').
      joins("left join lateral jsonb_to_recordset(items.meanings) as meaning_texts(meaning character varying) on TRUE").
      where('lower(meaning_texts.meaning) like ?', '%wash%').
      uniq #<-- b/c the left join will result in dupes

So you can get pretty fancy with this stuff. Things I learned:

• lateral joins like the one above can be used for any PG functions that return a set of items (and making rows of them with the join allows us to place conditions on them)
• jsonb_to_recordset(items.meanings) will take a JSON array of homogenous objects and create records for each as if each entry was a row in a table
• meaning_texts(meaning character varying) is like an anonymous table-type definition

Putting it all together

This is the ARel query I landed at initially (indexes to be figured out later)

    Item.where.not(:type => "radical").
      select('items.*, meaning_texts.meaning as meaning_text, reading_texts.reading as reading_text').
      joins("left join lateral jsonb_to_recordset(items.meanings) as meaning_texts(meaning character varying) on TRUE").
      joins("left join lateral jsonb_to_recordset(items.readings) as reading_texts(reading character varying) on TRUE").
      merge(
        Item.where('lower(meaning_texts.meaning) like ?', "%#{query.downcase}%").
        or(Item.where('reading_texts.reading like ?', "%#{query}%")).
        or(Item.where('characters like ?', "%#{query}%"))
    ).uniq