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:…
I'm facing issues with a jsonb ARRAY column in PostgreSQL. I need to sum this column for each row. Expected Result: index sum(snx_wavelenghts) 1 223123 2 223123
Is there a way to achieve this functionality for integer array? jsonb ?| text[] → boolean Do any of the strings in the text array exist as top-level keys or array elements? '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t…
I have a table "blobs" with a column "metadata" in jsonb data-type, Example: { "total_count": 2, "items": [ { "name": "somename", "metadata": { "metas": [ { "id": "11258", "score": 6.1, "status": "active", "published_at": "2019-04-20T00:29:00", "nvd_modified_at": "2022-04-06T18:07:00" }, { "id": "9251",…
Table CREATE TABLE users ( username VARCHAR(128) PRIMARY KEY, info JSONB ); INSERT INTO users (username, info) VALUES ('Lana', '[ { "id": "first" }, { "id": "second" } ]'), ('Andy', '[ { "id": "first" }, { "id": "third" } ]');…
I need to convert some element in jsonb column to array What I have: {"a": { "b": "2022-11-03", "c": "321321", "d": "213321" } } What I need: ` {"a": [ { "b": "2022-11-03", "c": "321321", "d": "213321" } ] }
I am using postgresql. Let's suppose I have this table name my_table: id | idcm | stores | du | au | dtc | ---------------------------------------------------------------------------------- 1 | 20447 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 2…
I am trying to get data from a jsonb column and need to cast an attribute from the payload to timestamp. But the query aborts when the value is null or invalid. This is my select statement. Select (jsonb_path_query(AnchorNode, '$.TestDate')#>>…
We have a PostgreSQL table with jsonb column. Some of the nodes in the json can come in as array or object in the input. I am trying to write a query that will give me the array length, if…
I've a JSONB inside a PostgreSQL table with this structure (more or less) { "obj1": { "obj2": { "obj3": [ { "obj4": { "obj": "A" } }, { "obj4": { "obj": "B" } } ] } } } Then my…