PostgreSQL has many functions and operators for JSON data and we will explore some of these others in future articles. In this article we continued the work done in the prior article of querying the jsonb array of objects as a recordset by adding the ability to get specific array objects, add a new array object and remove array objects. If the percentage of the rows is smaller than 5-10% of all rows in the table the benefit of using the information stored in the index outweighs the additional intermediate step. Given a large enough number of requested rows (such as all the posts with an id greater than 10), it is actually more efficient to go fetch all those rows from the table directly rather than to go through the extra step of asking the index for the position of those rows and next fetch those specific rows from the table. Then, the requested rows (a subset of the total) are actually fetched from the table. When an index is used in a SELECT query, first the position of the requested rows is fetched from the index (instead of from the table directly). In our small data set you may not see the index being used when you do an Explain Analyze. Indexing a Key in the JSONB Columnįor a specific Key, name here, in the field use, CREATE INDEX idx_purchases_name ON public.purchases ((items_purchased -> ‘name’)) Indexing the Column CREATE INDEX idx_purchases ON public.purchases (items_purchased) Also recall that the -> returns a string so in item_object->’productid’ = ‘1’ we compare against the string version of the productid, even though it is a number in the json.īased on a couple of questions from readers, I have added indexing the items_purchased column and a Key value in the JSON.Even though the ordinal position is a bigint, we must Cast it to int or bigint.Therefore we used position-1 to get array location 1. We used a a query to find the position in Carol’s items_purchased for productid=1.This had ordinal position 2 but array position 1. SELECT * FROM purchases where id=1 Ĭarol without productid=1. First query for Bob’s items to see he has three, and then remove item 2. Let’s remove item number 2 from his items_purchased. Key/value pairs are matched based on their key value.īob returned his Mechanical Keyboard. The “-” operator description in the documentation is a bit vague in my opinion.ĭelete key/value pair or string element from left operand. When the position in the array is known, this is as simple as using the ‘-’ operator.But it is important to note that while this ordinal value starts with the number 1, a jsonb array of objects starts at 0. This works fine when using the jsonb_array_elements() function.
Important Note: Previously we used “with ordinality” to get “position” in the array. In our example, we substituted and empty array and then concatenated because you cannot concatenate on to null.
We often use the COALESCE function to substitute a default value for null values when we querying the data Our sample data in a jsonb column will look similar to this, [' ::jsonb WHERE id=1 We will not only look at the query techniques but think about these as being API end-points and what they might mean for the front-end in, say, a PERN stack.
0 kommentar(er)
