Skip to content

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”)

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 circumvent this I’ve handled duplicates in

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 first 5 rows

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” -> “Link”:”https://face.png”) like below: Answer You can use jsonb_set: See fiddle.

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 type because input has type unknown From here

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 optional boolean parameter is true. See: Store