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…
In a table called temporay_data with a data field called temporary_data too, filled with this JSON structure { "FormPayment": { "student": [ { "fullname": "name student1 ", "rate": 210, "meal": 7, "mealValue": 175, "finalValue": 385, "role": "student", "willPay": true },…
I would like to count distinct values from someText property from nested jbson object, but I can't figure out how properly do it. Any suggestions are appreciated. This is my jbson object { options: [ { "object": { "someText": "example1"}…
I want to query a JSONB array using a regex filter. Following the docs, I should be able to do: WHERE jsonb_path_exists( data, '$.value[*] ? (@ like_regex $foo flag "i")', jsonb_build_object('foo', 'demo') ) But I'm getting an error: ERROR: syntax…
I demonstrate the issue with the following simple table. (The actual table and JSON document have a lot more fields.) CREATE table contact ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR, lastname VARCHAR, birthday timestamp with…
I have a postgresql function that takes a number of parameters, the last of which is jsonb. It's called with the following value of parameters (double quotes due to postgres diagnostic output): SELECT vspm.rfd_add_f ( $1::bigint , $2::bigint , $3::bigint…
given a json_b array column named attributes that contains multiple json objects. how can i query the attributes column to get all those rows which does not have a specific value in any json object in the column. for instance,…
I have a table with a JSONB field and would like to insert into it using a named dict like so: sql = "INSERT INTO tbl (id, json_fld) VALUES (%(id)s, %(json_fld)s)" conn.execute(sql, {'id':1, 'json_fld': {'a':1,'b':false, 'c': 'yes'}}); I tried the…
I have a table listings with a jsonb column named data. Inside the data column I have the next structure { "attributes": { "listings": [ { "vin": "a", ... }, { "vin": "b", ... } ] } } I am…
I have a query that gets some data, for example: select type_of_client from table1; type_of_client -------------- restaurant bar cinema ... And I would like to use these results into a jsonb element, for example: select count(*) from table2 where fields->>'restaurant'…