Skip to content

Instantly share code, notes, and snippets.

@mankind

mankind/rails-jsonb-queries

Last active October 8, 2025 23:13
Show Gist options
  • Save mankind/1802dbb64fc24be33d434d593afd6221 to your computer and use it in GitHub Desktop.
Save mankind/1802dbb64fc24be33d434d593afd6221 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mankind revised this gist Mar 11, 2021. No changes.
  2. 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`

    ```
  3. 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")
    ```

  4. 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)

  5. 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")