Skip to content

Instantly share code, notes, and snippets.

@gnomex

gnomex/rails-jsonb-queries

Forked from mankind/rails-jsonb-queries
Created June 19, 2020 21:42
Show Gist options
  • Save gnomex/f380cf40397a0c8b2f6f5de28ba74a41 to your computer and use it in GitHub Desktop.
Save gnomex/f380cf40397a0c8b2f6f5de28ba74a41 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @mankind mankind revised this gist Aug 22, 2019. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion rails-jsonb-queries
    View file
    Original file line number Diff line number Diff line change
    @@ -38,7 +38,8 @@ Segment.where("tags @> ARRAY[?]::varchar[]", ["dele, jones", "solomon"])
    # tags with 3 items
    Segment.where("array_length(tags, 1) >= 3")

    # SUM
    # SUM (Thanks @skplunkerin)
    https://gist.github.com/mankind/1802dbb64fc24be33d434d593afd6221#gistcomment-2711098
    `https://stackoverflow.com/a/39280048/1180523`

    ```
  2. @mankind mankind revised this gist Aug 22, 2019. 1 changed file with 10 additions and 1 deletion.
    11 changes: 10 additions & 1 deletion rails-jsonb-queries
    View file
    Original file line number Diff line number Diff line change
    @@ -36,4 +36,13 @@ Segment.where('NOT (? = ANY (tags))', 'solomon')
    #multiple tags
    Segment.where("tags @> ARRAY[?]::varchar[]", ["dele, jones", "solomon"])
    # tags with 3 items
    Segment.where("array_length(tags, 1) >= 3")
    Segment.where("array_length(tags, 1) >= 3")

    # SUM
    `https://stackoverflow.com/a/39280048/1180523`

    ```
    #data: [{"amount"=>12.0},{"amount"=>25.50},{"amount"=>17.99}]
    Segment.select("SUM((data ->> 'amount')::FLOAT) AS total_amount")
    ```

  3. @mankind mankind revised this gist Feb 9, 2017. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions rails-jsonb-queries
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,6 @@
    http://stackoverflow.com/questions/22667401/postgres-json-data-type-rails-query
    http://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails

    #payload: [{"kind"=>"person"}]
    Segment.where("payload @> ?", [{kind: "person"}].to_json)

  4. @mankind mankind created this gist Feb 9, 2017.
    36 changes: 36 additions & 0 deletions rails-jsonb-queries
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    #payload: [{"kind"=>"person"}]
    Segment.where("payload @> ?", [{kind: "person"}].to_json)

    #data: {"interest"=>["music", "movies", "programming"]}
    Segment.where("data @> ?", {"interest": ["music", "movies", "programming"]}.to_json)
    Segment.where("data #>> '{interest, 1}' = 'movies' ")
    Segment.where("jsonb_array_length(data->'interest') > 1")
    Segment.where("data->'interest' ? :value", value: "movies")
    Segment.where("data -> 'interest' ? :value", value: ['programming'])

    data: {"customers"=>[{:name=>"david"}]}
    Segment.where("data #> '{customers,0}' ->> 'name' = 'david' ")
    Segment.where("data @> ?", {"customers": [{"name": "david"}]}.to_json)
    Segment.where("data -> 'customers' @> '[{\"name\": \"david\"}]'")
    Segment.where(" data -> 'customers' @> ?", [{name: "david"}].to_json)

    #data: {"uid"=>"5", "blog"=>"recode"}
    Segment.where("data @> ?", {uid: '5'}.to_json)
    Segment.where("data ->> 'blog' = 'recode'")
    Segment.where("data ->> 'blog' = ?", "recode")
    Segment.where("data ? :key", :key => 'uid')
    Segment.where("data -> :key LIKE :value", :key => 'blog, :value => "%recode%")

    #tags: ["dele, jones", "solomon"]
    # get a single tag
    #Segment.where("'solomon' = ANY (tags)")
    # which segments are tagged with 'solomon'
    Segment.where('? = ANY (tags)', 'solomon')
    # which segments are not tagged with 'solomon'
    Segment.where('? != ALL (tags)', 'solomon')
    # or
    Segment.where('NOT (? = ANY (tags))', 'solomon')
    #multiple tags
    Segment.where("tags @> ARRAY[?]::varchar[]", ["dele, jones", "solomon"])
    # tags with 3 items
    Segment.where("array_length(tags, 1) >= 3")