I have an SQL table laid out like so Column Type Modifiers uuid uuid not null name character varying type character varying info jsonb created bigint Inside the info column is numerous things such as {"id": "1402417796043342360", "colour": "blue", "subtype":…
I have a table in Postgres with the structure bellow. id name objects(jsonb) 1 first [{"name":"a", value:"1", "param":{}}, {"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}] 2 second [{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}] 3 third [{"name":"c", "value":"345", "param":{}}] | 4 forth…
I have a DB function, say "myWrapperFunction"(), which returns a JSON array of objects as below: select * from "myWrapperFunction"() >> [{"a":"b","c":"d"},{"e":"f"}] I have a table "table1" with column 'column1'. I need to pass the values from table1.column1 into myFunction()…
I have postgresql table with a jsonb column containing maps with strings as keys and string arrays as values. I want to aggregate all the maps into a single jsonb map. There should be no duplicate values in string array.…
So I work with Postgres SQL, and I have a jsonb column with the following structure: { "Store1":[ { "price":5.99, "seller":"seller" }, { "price":56.43, "seller":"seller" } ], "Store2":[ { "price":45.65, "seller":"seller" }, { "price":44.66, "seller":"seller" } ] } I have…
currently I'm struggeling with PostgreSQL 11, JSONB and database indices. We have a customers table (shortened): create table public.customers ( id uuid not null, created timestamp without time zone, lastmodified timestamp without time zone, data jsonb, primary key (id, mandantid)…
I have a table which stores timestamps but they're in a jsonb array column like this: id cycles 5 [{"end": "2022-10-18T18:31:34.529667Z", "start": "2022-10-05T19:01:51.400124Z"}, {"start": "2022-10-28T08:27:13.682084Z"}] 6 [{"start": "2022-10-03T16:37:38.119236Z"}] 7 [{"end": "2022-11-14T11:30:17.964960Z", "start": "2022-11-08T19:20:20.413133Z"}] I need to the values from the…
I want to be able to extract text from jsonb array in a new column as text. This is my SQL table. Id. ErrorCode. 101 ["exit code: 1"] 102 ["exit code: 3"] 103 ["OOMKILLED"] This is my column definition '[]'::jsonb…
I'm trying to update a jsonb array in Postgres by replacing the entire array. It's important to note, I'm not trying to add an array to the object, but simply replace the whole thing with new values. When I try…
I want to query ActiveRecord collection and select records, where string value inside jsonb field is included in a given array. Model: create_table "dishes", force: :cascade do |t| ... t.jsonb "params" ... end Content of params always has this structure:…