Skip to content

Tag: jsonb

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

Filtering out objects from multiple arrays in a JSONB column

I have a JSON structure with two arrays saved in a JSONB column. A bit simplified it looks like this Schema: Since each row (data column) can be fairly large, I’m trying to filter out matching item objects and group objects from the items and groups arrays. My current query is this which returns rows containing either the matching group

simplifying postgres queries

Note: Asking to improve the working code. My data column is as follows In the above data, I want to set available to false and price to zero. To do this I Am using the below code. My question is it possible to update both values in a single query? Thanks. Answer Sure:

How to select records from a Postgres table using an ORDER BY statement on a jsonb column that points to another table

I have two tables, characteristics and students. characteristics students The characteristics table has three columns where the id column is an auto-increment field. The name denotes the name of a specific characteristic and the value indicates the respective information about the characteristic. The students table contains detail about each student. The character_value field in the students table is a jsonb

PostgreSql format jsonb array

Can not find a proper query to format jsonb output in postgres I have a jonb column in a table. How do I select the posts column to have just certain fields? Something like: Any ideas? PS: The version of postgres is the latest 12, 13, 14 … Answer You may considering using json_to_record_set to extract your desired columns for

Format JSONB column by taking the text value from same jsonb coulmn

CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values (‘dummydata-got-uploaded’); I need to correct the jsonb column value with below query. update test set data={“addDet”: data }::jsonb where id =1; ERROR: syntax error at or near “{” LINE 1: update test set data={“addDet”: data… Expected: id | data 1 | {“addDet”: ‘dummydata-got-uploaded’ } ` Thanks in advance. Answer you

How to get JSONB array index

I’m sure there’s a simple answer to this but I just can’t find it. I need to obtain JSON array elements as rows, but their index is relevant for the subsequent processing. Here’s a [very simplified] example: I need to get: I’ve tried: But the section column is not computed correctly, as shown below: Answer You can use with ordinality