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
Tag: jsonb
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:
SQL error 42P01 when using jsonb_to_recordset function
I’m trying to display the contents of the jsonb column using the jsonb_to_recordset function: But get the following error The function itself works when I substitute the values manually. Сan’t figure out what the problem is. Answer Write it as a FROM…JOIN:
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 join table on json property and get oldest first from result with nulls first
I got 2 tables; domains and events. Im trying to create a query that returns a list of distinct domains that is ordered by the oldest events (for that domain) with nulls first and distinct domain. Basically this query will do the job: But the output is not distinct on ‘domain’. When using ‘distinct on’, it gives the wrong output
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
Convert list of dictionaries into dictionary in Postgres jsonb
I have an actions_table looking like: I would like to aggregate by profile_id and jsonify as: The closest I could get to is: By means of the query: How do I get to my desired result from here (convert list of dicts into dict), or how do I fix my original query? CAVEATS I need a general answer, I might