I have a jsonb column which have following rows ROW1: [ { “cpe23Uri”: “cpe:2.3:a:sgi:irix:3.55:*:*:*:*:*:*:*”, “active”: true }, { “…
Tag: jsonb
Query for array values within jsonb column
I have a jsonb column called chores in my work table with data looking something like this: [{“task”: “feed_dog”, “value”: “Daily”},{“task”: “mop_floor”, “value”: “Weekly”] There could be zero to dozens of tasks in…
How can I add condition based on select value (JSONB) on PostgreSQL 12?
I want to filter rows that have gold badges. I am using PG12 and I use new path feature of it. To clarify, I have a users_tbl table like this: CREATE TABLE users_tbl ( ID serial NOT NULL PRIMARY …
Aggregation of Json objects on a relational table on Postgres
I have a relational table that contains three variable values, an ID, and the date broken down into parts. ID | Year | Month | Day | A | B | C | 1 2019 12 01 5 10 15 1 2019 …
LIMIT and Contains in a single JSONB Query
I have 2 queries that separately run perfectly: This one limits the results: SELECT jsonb_agg(elem) as data FROM ( SELECT * FROM file_data, jsonb_array_elements(file_data) a(elem) LIMIT 3 ) …
Find rows based on nested key in jsonb array
I have a jsonb column in Postgres 9.6 that contains JSON arrays of the following form: [ { “courses”: { “course-1”: { “graduated”: false }, “course-5”: { “graduated”: true } } }, { “…
JSONB sort aggregation
I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …
Query JSONB column for any value where =?
I have a jsonb column which has the unfortunate case of being very unpredictable, in some cases its value may be an array with nested values: [“UserMailer”, “applicant_setup_3”, [“5cbffeb7-8d5e-4b52-…
Update jsonb column with value from row_to_json()
I have a table containing a data that look like: col1 col2 col3 col4 json_data —————————————————- a b c d {“mock”:”…
Update key of object inside array of objects in jsonb column
I have a jsonb column called data. It is deeply nested and there’s a key which’s value is an array of objects: As you see, there are different forms of “b” key. My goal is to update all rows with “b:” and “b_” keys and set them to “b”. Answer This an…