GIN indexes could technically be applied to an array! But it does not help much in this particular situation.Ī GIN index here would only be useful if we wanted to search for the whole of the content of an object inside the array - we would have to search for the entire object ( code, expires_in, extra) instead of just searching for the code. Total query time while obtaining coupon codes for a user Note: Assume project_backer is the same as user. Query and query plan to obtain coupon codes from a user With around 189k users, with around 6k of them having codes, the query takes somewhere between 8msec to complete, which is not ideal… WHERE obj->'code' ilike '%search_term%') Efficiency For each user, it checks whether any existing code matches, and the final query is actually pretty simple! SELECT * I was able to do this by modifying the previous query a bit and using the EXISTS operator to test for the existence of rows in the subquery. However, the final goal is to return the users associated with these codes, so there is a final extra step. We’re making progress here! The previous query returns to me all the codes matching my search term. Now, to find a specific coupon with a code, we use this previous query as a subquery and use the JSON operator inside the WHERE condition: SELECT * FROM Well… this seems to work to retrieve all the user’s coupon JSON objects! Setting out to find an alternative, I found the unnest(anyarray) function, which does basically the same function, but this time it works directly with the array! SELECT unnest(upons) Internally, the function looks at the JSONB object and determines if there are any set of objects, but still, it does not work directly with the type we set for our coupon column… However, this function seems not to work with our JSONB type, only JSONB… Bummer □! Searching through the same JSON functions documentation as shown above, I found an interesting function, which was supposed to unroll a JSONB into a set of values: jsonb_array_elements(jsonb). Now, to find a way to actually do this but for an entire array! How do we do this for a whole array? This operand is nifty and useful, but here, I only used it directly on a single field. The coupon, which included some closely related variables looked something like this:Ĭoupon - JSONB -> 'code' In this case, I actually used JSONB - a decomposed binary form of JSON - since it can be more efficient. In PostgreSQL, this can be done by defining a column as JSON. Additionally, a JSON field might be useful when the structure of an object might vary. I saw an opportunity here to, instead of just creating a new coupon table, to directly create a coupon field on the user table, saving me some trouble in extra queries and joins. Recently, in a project I was working on, Backercamp, I had to implement a Shopify integration feature where some users would be attributed some coupons to use. Why did I need a JSON field in the first place? This post shortly describes how to create embedded schemas in Ecto to save a JSON data structure on your database, and how to implement a direct database search on this same field. This is possible and supported by both PostgreSQL and Ecto, and it can sometimes simplify your database in cases where you absolutely want to store some kind of nested data structure. Have you ever wanted or had the need to take a database table and cram a lot of information into a specific field using JSON?
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |