I have a Postgres db containing a table with a jsonb column tsent that contains the results of targeted sentiment analysis of text in another column. The data is structured like so: CREATE TABLE tbl(id int PRIMARY KEY, tsent jsonb);…
I'm a newbie in backend development and just started to build a simple table including jsonb type with PostgreSQL in node.js on Ubuntu. CREATE TABLE testTable ( id SERIAL NOT NULL PRIMARY KEY, vStr VARCHAR(64), jVar jsonb); Then I tried…
How do we query for a particular fields in an array element inside a json string The column datatype is JSONB Sample json { "data" : [ { "begin" : "..." }, { "inter" : "..." }, { "inter" :…
SELECT BTRIM('"enterprise"', '"') > Output = enterprise I need to use BTRIM function to trim the double quotes like above, with jsonb_array_elements function. I have a query like below, SELECT jsonb_array_elements(json_column->'Fields')->'field_name' as "column" FROM table_a Which returns column "Value1" "Value2"…
I am working on PostgreSQL 13.9. I want to check an array within a JSONB with another input array to see if there was any overlap of elements between the two then return true in the sample SQL below select…
I have a deeply nested json array. The array contains a list of json objects, I want to remove any of the objects that are matched based on a field within the object. { "name": "John smith", "items": { "unknown-key-1":…
Working with jsonb and Postgres I'm running into the following problem: My output looks like this: [ {columnName: {}}, { columnName: {} } ] But I would like it to look like this: [ {}, {} ] I want to…
I am currently working on a function in Postgres. I have the function working, but I need to add a loop inside the json_build_array() function to add multiple of what is inside the array. This is what I currently have:…
I'm currently working on a PostgreSQL query to extract specific values from a JSONB column. Here's the query I'm using: select a.id, (jsonb_array_elements(a.info->'attribute1')->>'value') as attribute1, (a.info->>'attribute2') as attribute2, (a.info->>'attribute3') as attribute3, (jsonb_array_elements(a.info->'attribute4')->>'value') as attribute4 from a_table a where (cast(a.info->>'attribute3' as…
I have a table with a jsonb column and I want to migrate every record to a new schema. Old schema: {"foo": "bar"} New schema: {"foo": {"value": "bar"} The bar value varies by row and I want to preserve that…