I am using postgresql. Let’s suppose I have this table name my_table: I want to select only the rows having the value of id equal to one of the elements of the array in stores (of that line). However, the type of stores is not array, it is jsonb. So I want to get something like this: I have tryed
Tag: jsonb
SQL get the value of a nested key in a jsonb field
Let’s suppose I have a table my_table with a field named data, of type jsonb, which thus contains a json data structure. let’s suppose that if I run I get so in pretty formatting, the content of column data is I know that If I want to get directly in a column the value of a key (of “level 1&…
SQL: extract keys and values of a jsonb field as rows of two separate columns
In my postgresql DB I have the following query returning where json_field is of type JSONB. I am looking for a way to query all the keys and all the values from this json field, so that the output would be Is there a way to do it ? generalizing the question after it got the solution answer When I
What’s the best way to “append” to a list of strings stored in a JSONB column in a table PostgreSQL such that duplicates are not stored
Say there is a table employee One of the rows has the following information in the tags column Currently to update the column with additional values I perform the following query However this adds duplicates to the cell and makes it look like which is less than desirable for my use case. Currently to circumve…
Order and limit data based on JSONB array
I have such a PostgreSQL table Now I would like to get all sorted val and have the limitation for each query (for pagination). For example 1st query 2nd query Answer You need to unnest the array, sort the result then apply offset and limit: Instead of limit 5 you can also use the standard compliant fetch firs…
How to append nested key-value to json array in postgresql?
My json values in jsonb column are below: I have nested key – “Images” that has array of strings. How to write query that will add to Images new keys/values (“UploadedBy”:null and “Size”:null) and transform array of string to key-value (“https://face.png” …
Postgres: Update jsonb from other table column which is encrypted
I am migrating a data from an encrypted jsonb column (app_id field) in a table to another jsonb column in a different table. As said: origin table column is encrypted. Destination one is not. Right now I have the following query: Error response is: SQL Error [42804]: ERROR: could not determine polymorphic typ…
Sum of column values inside Json array with group by
I have next Django model. I store JSON data in wh_data. Note: it’s data for one row – 2022-06-06. I need to calculate the sum inside stocks by grouping them by wh and by created_at so that the output is something like this I know how to group by date, but I don’t understand how to proceed wi…
How to group results by values that are inside json array in postgreSQL
I have a column of type jSONB that have data like this: column name: used_filters row number 1 example: row number 2 example: I want to group the result to get the most frequent value for each one of the keys something like this: key most_freq category economic tags eco-friendly keyword 2050 the keys are not …
How to convert integer[] to jsonb in a PL/pgSQL code block
How to convert integer[] to jsonb? Answer Use to_jsonb(): Or: array_to_json() is only still useful to get line feeds in json (not jsonb!). The manual: Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optiona…