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.

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

Expanding jsob array in PostgreSQL to produce analytics

Imagine that we have the following table using jsonb on PostgreSQL: And the following data is available: Is it possible to expand the JSON array to 2 columns similarly to what PowerBI expand does and get the following result? firstName surName bookName William Shakespeare Hamlet William Shakespeare Juliet Agatha Christie Hercule Poirot Agatha Christie Miss Marple instead of firstName surName