I am having a table where one column is a JSONB column that has the following payload: [ {a: "foo", b: "bar"}, {a: "bar", b: "baz"} ] I want to remove the "b" element for each array item: SELECT json_build_object(…
I have a table with columns in jsonb format which contains data like this: ['A', 'B', 'C'] I want to convert column to array column type but I don't know how to do it properly. Example of my script structure:…
I want to query the jsonb column in the below table, sum all fields named time, and then query for any object '"type": "Unknown" and also have a time that is greater than 10% of the previously calculated sum. CREATE…
I have the following table structure: create table system_log ( id bigserial primary key, details jsonb ); Data in 'details' column is json with inner json: { "prop1": "value1", "prop2": "value2", "body": "{"prop3": "value3"}" } How to select data from…
Suppose we have two columns: id, params. params is a jsonb column that contains arrays of objects like: [ { "prop": "a", "answer": "123" }, { "prop": "b", "answer": "456" } ] I need to create a query that returns…
I have a jsonb column in the following format: { "targets": { "01d7de90-57fd-4c4f-b7c9-3b956762fe25": { "id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25", "name": "target1" }, "0f43e1fe-132e-464b-8284-4a9947a70c1c": { "id": "0f43e1fe-132e-464b-8284-4a9947a70c1c", "name": "target2" } }, "suggestions": [ { "items": [ { "id": "foo", "code": "item1" } ], "groupId":…
I'm using ::text[] to convert an array inside a JSON column, like in the example below: Imagine sometable to have a JSON column called 'object' with a structure like the following: { "properties": ["string", "someotherstring"] } I am querying the…
I have a Postgres table named "base" with a jsonb field called "addresses" which stores multiple addresses for the same user. Here is the table structure: create table base(name text, addresses jsonb); insert into base(name, addresses) values('John', '[{"zip":"01431000","state":"SP","number":100,"street1":"Avenida Brasil","city_name":"São Paulo"},{"zip":"01310900","state":"SP","number":200,"street1":"Avenida…
I'm trying to run the following query, which unless I'm misunderstanding the documentation looks as like it should be possible. SELECT id, profile FROM end_user WHERE profile @? '$.first_segment_exited_at ? (@.datetime() < "2023-05-25".datetime())'::jsonpath; This is the documentation which leads me…
I want to run a query towards PostgreSQL where any attribute in a JSON body matching a list of attribute names should equal a given value. The input: names: ["foo", "bar"], value: 'hello' should render a query such as SELECT…