Ruby on Rails-5 postgresql-9.6 jsonb queries

rails-jsonb-queries

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)

#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")

# SUM (Thanks @skplunkerin)
https://gist.github.com/mankind/1802dbb64fc24be33d434d593afd6221#gistcomment-2711098
`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")
```

Thanks @valachi for enriching this gist by your addition.

Hi! How i can unscoped jsonb conditions?

My table order has a jsonb column called line_items that can contain 1 or more line items like this:

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  },
  {
    "id":9994857578581,
    "sku":"NATURAL-SHADOW_C1_24H",
  }
]

I need to find the count of all orders that have sku = CLIPPING-PATH_C2_24H && there is no second line item in it unlike the above one. So, the query won't count the above one but the one below:

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  }
]

Could you advise how to achieve this?

I was able to figure this out using the jsonb_array_length method:

Order
.where("line_items @> ?", [{sku: sku}].to_json)
.where("jsonb_array_length(line_items) = 1")
.count
end

This is extremely useful. Thank you so much for sharing.

Is there a way to search json field globally?

I don't know what's json data structure and I don't care at all.

I want to search all keys and all values inside the json field. Or, is there a way to flatten the json become a string and search the string?

For JSON arrays you can use json_array_elements_text to convert to text and search that. Otherwise I think that jsonb_pretty might work, as that convers the JSON content to text, which you can search.

I have structure like project_data:jsonb field in postgres table
project_data: [{"data"=>"1", "template_field_id"=>1}, {"data"=>"10-11-2022", "template_field_id"=>2}, {"data"=>"Hi", "template_field_id"=>3}, {"data"=>"abc", "template_field_id"=>8}, {"data"=>"2", "template_field_id"=>9}]

data field will always have string values.
template_field_id will always have integer values (i.e. id of another table).

I want to get list of all records based on data field and template_field_id field. Below criteria only applies to data field values.

1. Filter by date range (i.e. between 2 dates)
2. Filter having exact text
3. Filter having number between range, including number (i.e. data field having number between 3 to 10)
4. Filter matching partial value.
5. How to group (Grouping Query)

Could you advise how to achieve this with single and multiple filters?

also good article:
https://dev.to/sara_alhaddadi/insert-and-query-jsonb-in-rails-and-postgres-5dci