Questions tagged [jsonb]

Binary version of the json data type, used in Postgres 9.4+. The major practical difference is efficiency.

0
votes
0answers
5 views

updating postgres jsonb column

I have below json string in my table column which is of type jsonb, { "abc": 1, "def": 2 } i want to remove the "abc" key from it and insert "mno" with some default value. i followed the ...
0
votes
0answers
5 views

Convert Posgres JSON element to array containing elements

I have a JSON saved in a Posgres database with schema similar to this: ID TYPE TASK TASKS 1 SINGLE {"a": "b"} null 2 GROUP null [{"c":"d"}] I need to update all rows where type is ...
0
votes
1answer
19 views

Postgres JSONB multiple attribute matches

Say I have a JSONB object like this: {"First":"Joe", "Last":"Smith", "Age": "29", "cat":"meow"} I want to be able to find this JSONB object if I search just: {"First":"Joe", "Age":"29"} I tried ...
0
votes
0answers
11 views

javax.json.bind.JsonbException: org.apache.johnzon.jsonb.JohnzonProvider not found

Why i'm getting this error. It work fine in local glassfish server but when deploy to remote server it throws exception.
0
votes
0answers
12 views

jsonb type data to map<String,String> in my mapper class, but received exception “Expected a string but was BEGIN_OBJECT at line 1 column 2 path $”

I have written query to fetch two columns which is of JSONB type, and in my mapper class converted jsonb type to string and assigned to map<String,String>. As it can have multiple row returned ...
0
votes
0answers
11 views

How do I change jsonb object value while creating a view?

I have a table that I cannot update, we are creating another table for the rows that need a change. We want to create a view on top of these 2 tables and change one object value of jsonb column. I am ...
0
votes
1answer
24 views

Jsonb_set: How to Update ALL array elements with key

I have a table 'things' with a set of columns id | name | data 1 | 'hi' | [{name: 'what', amount: 10}, {name:'koo', amount: 15}, {name: 'boo', amount: 13}] I want to change the amount to 0, in all ...
0
votes
1answer
15 views

ERROR 42883: operator does not exist: text / text

When running the following query: select data from example where (data -> 'properties' ->> 'outageCount') / (data -> 'properties' ->> 'trackedCount') > 0.01 I'm getting the ...
1
vote
2answers
74 views

Composite JSONB array query in Postgres?

Table: test, JSONB column: content: create table test (id bigserial primary key, content jsonb); content contains a list of fixed-length lists: insert into test values (1, '[["first 1", "second 3"]]...
2
votes
1answer
79 views

JOOQ Code Generator skips a class that contains a JsonNode field

I'm currently working on a project that uses JOOQ for the sql transactions with a Postgres. But one of the models has a field of JsonNode type ( jsonb in postgres ). JOOQ code generator does not ...
1
vote
1answer
28 views

PostgreSQL datatype jsonb max storage range

I am running PostgreSQL version 9.6.13. I have 2 questions What is the max storage range/size of datatype 'jsonb' and 'text' How to find max storage range/size for other fields? I looked into '...
0
votes
0answers
37 views

Forming sql command with json object fails

I am trying to create sql command with json object that can be run by python script. I came up with below script. The method func1() works, but its not readable. func2() is the one I am aiming for, ...
0
votes
0answers
14 views

Mapping dynamic JSONB to fixed table in postgres

My problem is quite simple. I have a postgres function that returns multiple, flat JSONB objects with different keys as such: {key1: "value1a"} {key1: "value1b", key2: "value2b"} {key2: "value2c"} ...
0
votes
0answers
9 views

Receiving error parsing request.body in Django view from AJAX POST request

When trying to parse json and update db I'm receiving a json decode error, presumably because the json string is not being sanitized properly? An example result title=casdkj&paste%5B0%5D%...
0
votes
0answers
22 views

how to create select query using with

i need update data in colunm favorites: CREATE TABLE IF NOT EXISTS client ( id text UNIQUE NOT NULL CONSTRAINT crm_id_pkey PRIMARY KEY, favorites jsonb default '{}' ); values look ...
0
votes
0answers
22 views

Can I select all same fields of a json field regardless of their path

I have a lot of json files stored in a json data type column in postgres. Now there are plenty of places where the key "warning" can apply. Unfortunately I can not get a json schema so I can not know ...
0
votes
0answers
14 views

HQL way of writing where clause for JSON

I have a postgres database table with a jsonb column called data. I am looking for a way to write HQL in which I can write a where condition against the column data. I am aware of native way of ...
1
vote
1answer
19 views

How to get all keys ordered by key's value from jsonb postgresql

how to get all keys from json ordering by value of it's own key for example: key is a just number like uuid and value is the date i've already do this but without ordering: CREATE TABLE IF NOT ...
0
votes
0answers
6 views

Query based on jsonb column

I have a table that has a jsonb column (called info) which has two types of shapes: sometimes is an object and sometimes is an array of objects. I want to be able to get all the rows in that table ...
1
vote
1answer
15 views

Conditionally do jsonb_array_elements

I have a jsonb column called info that some type have the form of an object: { id: 2 } and some other types it has the shape of an array: [{ id: 2 }] I have a query that does this: SELECT * FROM ...
0
votes
1answer
27 views

How to make sql table column from jsonb int array

I have a table with jsonb array column like: name | arr --------------- n1 [1,2,3] n2 [4,5,6] I want to get a table from it like: name | element --------------- n1 1 n1 ...
0
votes
1answer
19 views

GIN index on PostgreSql jsonb column not being used in queries

I am using PostgreSql 9.6. (please don't ask me to upgrade - I must use 9.6) I have a table which has a jsonb column. I have created a GIN index on this column. The table has 320,000 records. A "...
1
vote
1answer
29 views

PostgreSQL param binding conflicts with JSONB operators

I'm updating a JSONB field in my postgreSQL database (using PHP Symfony DBAL) but I'm also using parameter-style data binding to remove the risk of SQL-injection. My query looks like this: UPDATE ...
1
vote
1answer
33 views

Update jsonb column with value from row_to_json()

I have a table containing a data that look like: col1 col2 col3 col4 json_data ---------------------------------------------------- a b c d {"mock":"...
3
votes
2answers
42 views

How to access internal representation of JSONb?

In big-data queries the intermediary "CAST to text" is a performance bottleneck... The good binary information is there, at the JSONb datatype: how to rescue it? Typical "select where" example: ...
0
votes
1answer
17 views

Adding more than one path item to a JSONB field in PostgreSQL

I have a jsonb field in a table and I want to add data to it. The record exists but I don't know if the json structure exists. UPDATE car SET features = jsonb_set( features, ...
0
votes
1answer
15 views

get tag with json ot jsonb query

I am using PostgreSQL 11. I'm trying get "wmnote" tag from this json(this is a fragment, it is necessary to close the labels): { "order": [ { "notes": { "note": [] }, ...
0
votes
0answers
21 views

Transforming jsonb data to a structured table efficiently

I have a few hundred records containing the following data: { "participants": [ { "name": "xxxx" }, { "name": "xxxx" } ], "messages": [ { "sender_name": "...
1
vote
1answer
13 views

Merging two joined tables and output as JSON?

I want to SELECT entities and an uploads JSON array column containing all of their uploads, along with the cover and profile fields for each upload. So far, I can get the array of uploads, but I am ...
1
vote
2answers
32 views

How to extract key and value from json column in Postgres?

I want to add key-value pairs extracted from json column to table with Postgresql. I have an items_table with uid and data column in json with multiple pairs. Initial table that looks like: uid | ...
-1
votes
1answer
22 views

Jsonb object parsing in PostgreSql

How to parse jsonb object in PostgreSql. The problem is - object every time is different by structure inside. Just like below. { "1":{ "1":{ "level":2, "nodeType":2, ...
1
vote
2answers
40 views

Rails PostgreSQL jsonb GROUP BY query on internal dynamic keys

I have an ActiveRecord model let's call it API::Response and within that we have column named error_messages which stores the error messages when the API call fails. The structure of API::Response is ...
0
votes
0answers
30 views

How to let Json-b deserialize generics object

I am using json-b (javaee-8.0) with yasson (1.0.3) implementation to deserialize a json document. It works with raw class type. If the data object contains generic types, I don't know how to let jsonb/...
0
votes
1answer
50 views

pgAdmin: Casting null JSONB to JSON returns connection closed

I was testing out some basic operations while working on a query to perform operations on a JSON column. When I ran this query: SELECT NULL::jsonb::json I received the following error message: Not ...
0
votes
1answer
27 views

In Ruby on Rails is there a better way than using eval() to accses this jsonb dynamically?

I've got some jsonb in a database and I want to write to an attribute dynamically, I know the path to the attribute but it could be any depth and any attribute name. I know the path to the attribute, ...
0
votes
0answers
20 views

How many times all rows got effected when we update the array of row?

for every request we will update the array with in the record for every records, so i want to know how many times those records got effected. ex: column names : _id, data with in data we have below ...
0
votes
0answers
28 views

SQLAlchemy filter according to list elements in JSONB

I have a JSONB column defined in Postgres and data inside looks like: {'related_ids': ['a', 'b', 'c', 'd']} Currently, I have two records one has "data" like: {'related_ids': ['a', 'b', 'c', 'd']} ...
0
votes
1answer
11 views

Querying data by empty json or with error data

I have a table in Postgresql that have a jsonb column. This column can have 3 types of data, in the following structure: [] if the result is empty; [{"items": [{"name": "item1", "value": "value1", "...
0
votes
1answer
30 views

Postgresql get keys from nested array of objects in JSONB field

Here' a dummy data for the jsonb column { "address": [ { "country": "US", "extension": [ { "extension": [ { "valueDecimal": -71.024638, "url": "latitude" ...
0
votes
1answer
37 views

How to search with jsonb operator “@>” in feathers.js/knex service

Using feathersJs/Knex and Postgresql. The (simplified) SQL query is this: SELECT * FROM projects WHERE team_members @> '{"members":[{"id": 1}]}'; How can I implement this request in feathers/...
1
vote
1answer
35 views

Postgresql get keys from array of objects in JSONB field

Here' a dummy data for the jsonb column [ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "...
1
vote
2answers
35 views

PostgreSQL jsonb - omit multiple nested keys

The task is to remove multiple nested keys from jsonb field. Is there any way to shorten this expression without writing a custom function? SELECT jsonb '{"a": {"b":1, "c": 2, "d": 3}}' #- '{a,b}' #-...
-1
votes
1answer
11 views

JSONB: How do I find all records which has keys in attribute with name “2” or “23”?

I have a JSONB hash structure in an attribute called data. I'm trying to figure out how I can find all records which has a key-name of EITHER "1" OR "23" among the keys in the hash? http://sqlfiddle....
0
votes
1answer
41 views

Convert jsonb comma separated values into a json object using a psql script

I have a table in postgresql that has two columns: Table "schemaname.tablename" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+---...
0
votes
1answer
42 views

JPA and JSON operator native update query

I'm trying to make this query work in JPA: UPDATE billing_log_criteria SET value = jsonb_set(value, '{cubPersonId}', '"12778269"') where value ->> 'cubPersonId' = '12778268'; It works ...
0
votes
0answers
19 views

Update jsonb array value stored as text in Postgres

Postgres Version: 9.5.0 I have a database table where one of the columns is stored as text that represents a json value. The json value is an array of dictionaries e.x.: [{"picture": "XXX", "...
1
vote
1answer
45 views

Strip empty objects on generated json/jsonb on Postgresql

I stip all the nulls using json_strip_nulls easily but it causes to have some empty objects on the results: { "id": 1, "organization_id": 1, "pairing_id": 1, "location": {}, "device": { ...
0
votes
1answer
25 views

Use a Postgres trigger to record the JSON of only the modified fields

Is there a way to get the JSON of the only modified fields? Now I use the following trigger but the entire line is printed in the changelog. Example tables: TABLE tbl_changelog ( tbl TEXT, ...
2
votes
1answer
40 views

PostgreSQL : Update multiple inner jsonb objects fields with same name

I have a table named test which has 2 columns : (id int, md jsonb). md column can contain data like this { "a": { ... "author": "alice" ... }, "b": { ... "author": "alice" ...
1
vote
1answer
47 views

PostgreSQL array formatting in functions

I need to change the array format from [1,2] to {1,2}. I wrote a function for that: CREATE OR REPLACE FUNCTION core.format_array( parray integer[]) RETURNS integer[] LANGUAGE 'plpgsql' ...