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

@Mansh05,
I don't have an immediate answer but I hope this links help:
https://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails
https://stackoverflow.com/questions/28486192/postgresql-query-array-of-objects-in-jsonb-field

Please post your solution here to help others who stumble on this gist in future.

@mankind, Thank you for redirects. I changed my data structure a bit just to make it work.

Now my data structure is like this
filters: { data: ["10", "20", "30"] }
filter is a column a the table with type jsonb.
With this i had to solve an or match query so that what ever is passed, at least on of the values should be there.

example: filter that are requested are ["20", "40"].

so two ways

1. This one will look for all the values contained in the database like given above.
   segment.where("filters @> ?", {data: ["20", "40"]})
   The above will return null as there is no 40 in it. If we had ["20", "30"] than we could get one value.

2. If we want to retrun the value even if only one is there than we can do
   segment.where("(filters -> :key)::jsonb ?| ARRAY [:value]", key: 'data', value: ['20', '40'])
   This will return the value if there is atleast on present in the database.

The structure like {key: 'a', display: 'trial'} is not a good structure to keep as we have to pluck or select each value of a key to another array and query there. This will bring n*2 query which is bad.

So my suggestion after running a performance testing is that whenever we save the data to jsonb object, parse it in such a way that we just need to compare the real query that would come and save it .

Example:
My real data stuct is
values: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}]
Than in your db save it like this.

Table displayers
columns: value:jsonb array: true, filter:jsonb
1 row- value: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}], filter: { keys: ['a', 'b'] }

Now you can query displayers.filter -> keys with

displayers.where("(filter -> :key)::jsonb ?| ARRAY [:value]", key: 'keys', value: ['a', 'c'])
this will retrun row 1.

I cannot write this in stackoverflow as i m not allowed to write an answer.

Thanks and please let me know if this is not clear.

Thanks! You saved me a lot of time :)

FWIW - nested structure query examples would also be helpful

@mankind, Thank you for redirects. I changed my data structure a bit just to make it work.

Now my data structure is like this
filters: { data: ["10", "20", "30"] }
filter is a column a the table with type jsonb.
With this i had to solve an or match query so that what ever is passed, at least on of the values should be there.

example: filter that are requested are ["20", "40"].

so two ways

1. This one will look for all the values contained in the database like given above.
   segment.where("filters @> ?", {data: ["20", "40"]})
   The above will return null as there is no 40 in it. If we had ["20", "30"] than we could get one value.
2. If we want to retrun the value even if only one is there than we can do
   segment.where("(filters -> :key)::jsonb ?| ARRAY [:value]", key: 'data', value: ['20', '40'])
   This will return the value if there is atleast on present in the database.

The structure like {key: 'a', display: 'trial'} is not a good structure to keep as we have to pluck or select each value of a key to another array and query there. This will bring n*2 query which is bad.

So my suggestion after running a performance testing is that whenever we save the data to jsonb object, parse it in such a way that we just need to compare the real query that would come and save it .

Example:
My real data stuct is
values: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}]
Than in your db save it like this.

Table displayers
columns: value:jsonb array: true, filter:jsonb
1 row- value: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}], filter: { keys: ['a', 'b'] }

Now you can query displayers.filter -> keys with

displayers.where("(filter -> :key)::jsonb ?| ARRAY [:value]", key: 'keys', value: ['a', 'c'])
this will retrun row 1.

I cannot write this in stackoverflow as i m not allowed to write an answer.

Thanks and please let me know if this is not clear.

Thanks!

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