The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [jsonb]

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

0
votes
1answer
18 views

PostgreSQL JSONB - Update key name on all rows with a single query

I have a jsonb field in a PostgreSQL table and I want to change only the key name, keeping the same value for it. Currently, I'm doing this in Rails like this: Event.each do |event| event....
1
vote
3answers
40 views

How to update a value inside nested arrays in a jsonb object?

I got the following jsonb content in PostgreSQL: { "segments": [ { "type": "year", "settings": [ { "name": "length", ...
0
votes
2answers
32 views

PostgreSQL: Updating attributes of elements in nested arrays within JSONB structure

I have a jsonb structure in PostgreSQL 9.6 that contains a nested array structure similar to the example below: continents:[ { id: 1, name: 'North America', countries: [ ...
0
votes
1answer
16 views

How to delete an array of keys from a Postgres jsonb object?

I have a jsonb object that I want to remove keys from. I have a jsonb array that holds the keys that I want to remove from the object. I see documentation for deleting a single key, like this: SELECT ...
0
votes
1answer
23 views

How do I extract values from a jsonb array of objects?

I am using Postgres and I am trying to get all the elements in the array that's has a keys statusCode and place the values into an array so I can display it. "systemStatuses": [ { ...
0
votes
2answers
23 views

Indexing PostgreSQL JSONB Array Elements

Like the title says, how can I index a JSONB array? The contents look like... ["some_value", "another_value"] I can easily access the elements like... SELECT * FROM table WHERE data->>0 = '...
0
votes
0answers
27 views

How to select from a jsonb with a concatenated text key?

I have a jsonb named attr with a key in the format of this.is.a.x Where x can be many other strings. Why do I get a NULL return for the following query: SELECT attr->>'this.is.a.'||x FROM ...
0
votes
0answers
20 views

Aggregate results on a recursive query with categories

I have a basic CATEGORIES-like table consisting of fields like the primary_key, a parent_id, title etc. I am able to retrieve the results using CTE and convert them to a json array but I want to ...
0
votes
1answer
19 views

How to query a particular object from a json array in PostgreSQL

I have a table called flow_flowrun and an array of json objects under a column called EVENTS like so: [{"msg": {"urn": "tel:+1238948372", "text": "Would you like to JOIN, reply now", "uuid": "34567a-...
0
votes
1answer
29 views

ecto jsonb array and map cast issue

I want to store data using jsonb that could be either an array or a map. The source data arrives as either an array or a map, and we want to unify this on the server so the front-end can work with a ...
1
vote
1answer
31 views

What does mean the “[XX000] ERROR: failed to build any 3-way joins” Postgresql error?

I have a table with a JSONB field containing nested arrays. I would like to explode the data in those nested JSON arrays to multiple rows while keeping the information about the place of each value in ...
0
votes
1answer
10 views

Aggregate jsonb unique values in one field in postgres

I have a dataset like this: id | recipients_by_data ------------+------------------------------------------------------------ 1000000001 | {"...
1
vote
1answer
22 views

How to query objects in nested json in postgres

Lets say I have table called users with jsonb column called attrs with values like this: { "uuid5": { "label": "Email", "value": "[email protected]" }, "uuid6": { "label": "Last ...
2
votes
1answer
43 views

Access JSONB fields as normal ActiveRecord attributes

I have a column on my model User called properties which is jsonb. As i have the requirement for user generated dynamic fields. How would you access the attributes as if they were normal mapped ...
0
votes
0answers
7 views

How to serialize/deserialize json-b bound objects from/to json when json is streamed?

I have the following question about the java api for json binding (json-b): Can you somehow serialize or deserialize objects from/to JsonParser/JsonGenerator instances? Or, put othervise, I am aware ...
1
vote
1answer
33 views

How to index a PostgreSQL JSONB flat text array for fuzzy and right-anchored searches?

PostgreSQL version: 9.6. The events table has a visitors JSONB column: CREATE TABLE events (name VARCHAR(256), visitors JSONB); The visitors column contains a "flat" JSON array: ["John Doe","...
0
votes
1answer
27 views

Postgres update INTEGER column from JSONB

I have a table cart: id | value | metadata --------+-------+------------------- 45417 | 0 | {"value": "1300"} 45418 | 0 | {"value": "1300"} 276021 | 0 | {"value": "1300"} ...
0
votes
0answers
16 views

How to write a query which includes an expression for JSONB_SET function's PATH attribute that contains string comparison in POSTGRESQL

I will post my use case here [ { "type": "space", "selected": "p01", "uuid": "3132ef1b-5778-4fd3-9b0f-fa5efa30bfa7" "nested": [ { "type": "wall"...
0
votes
1answer
22 views

how to insert the attributes of a postgres jsonb field into a key-value table?

I want to turn jsonb values into a key-value table within a single statement My sample code: CREATE TABLE public.searchdatacache ( querysig text, "values" jsonb ); INSERT INTO searchdatacache (...
0
votes
0answers
25 views

How to query JSONB column (storing array of objects) in Postgresql using sequelize in nodejs?

I am having a JSONB data type column name contributors in my table where i store a array of Objects, Ex: [{"user_id" : 1 , name : "user1" , ...} , {"user_id" : 2, name : "user2", ...} ,...] I have ...
0
votes
1answer
27 views

PostgreSQL return JSON objects as key-value pairs

A PostgreSQL instance stores data in JSONB format: CREATE TABLE myschema.mytable ( id BIGSERIAL PRIMARY KEY, data JSONB NOT NULL ) The data array might contain objects like: { "...
1
vote
1answer
35 views

Get counts of JSONB properties

I have jsonb column answers which may look like this for a 3 rows: {answer1: {label: "", value: "answer1_value1"}, answer2: {label: "", value: "answer2_value1"}} {answer1: {label: "", value: "...
1
vote
1answer
44 views

ERROR: operator does not exist: jsonb[] -> integer

select id,rules from links where id=2; id | rules ...
0
votes
3answers
30 views

Store and update jsonb value in Postgres

I have a table such as: ID | Details 1 | {"name": "my_name", "phone": "1234", "address": "my address"} 2 | {"name": "his_name", "phone": "4321", "address": "his address"} In this, Details is a ...
0
votes
1answer
22 views

Spread Syntax JSONB column in postgres

How do I do the spread syntax in postgres UPDATE UPDATE TABLE <table_name> set data={...original, ...<new_data>} where id='<id>' in postgres
0
votes
1answer
30 views

jackson: saving part of original message to jsonb

i receive a json message. i deserialize most fields but there is one field i don't have to deserialize but i have to save into db. it can be any json object: { "important_field": 123, "...
1
vote
0answers
29 views

get longest string in jsonb column

I have a postgresql jsonb column that hold a bunch of demographic data, something like {'city':'Mesa','county':'Maricopa'} Is there an efficient way to get the longest county name in the database? ...
1
vote
2answers
37 views

nginx + PostgreSQL: return JSON object

I like to run a PostgreSQL 10 server as a backend and nginx with ngx_postgres as a frontend. The database stores data in JSONB format: =# CREATE TABLE dump ( id bigserial primary key, data ...
1
vote
0answers
27 views

How to retrieve JsonbPair values of input JSONB object in PostgreSQL?

I am trying to write a PostgreSQL (11.2) server side function to read the key-value pairs of an input JSONB object. I did this (in print_kv_pair below) by trying to extract the JsonPairs from the ...
1
vote
1answer
20 views

Postgres sum of array stored in jsonb

I have a postgres database where some data are stored as jsonb arrays: id | start | duration | value ----+------------------------+--------------+------------ 1 | 2019-...
1
vote
1answer
29 views

Many to many tables combine in arrays

How can i SELECT book in this format: { title: 'Avengers', description:'Good book', tags: [{id:1, name: 'Drama'},{id:2, name: 'Horror'}] authors: [{id:1, name: 'Alex'},{id:2, name: '...
0
votes
1answer
14 views

Rails nested jsonb query

i have active record object like this `<BlockExercise id: 1, type: nil, user_id: 1, number: "S51_1", status: "close", exercise_distribution: {"Day1"=>{"BN3"=>"", "SQ1"=>"", "date"=>"10/...
0
votes
0answers
17 views

How to search in jsonb type database column pgsql?

I have a jsonb data type column. Here is data base $table->increments('id'); $table->integer('user_id'); $table->jsonb('data')->nullable(); I want to search inside data column. Here is ...
0
votes
0answers
29 views

jsonb postgres get select array row anidate child json

Hi i have a json with this structure "units":[ "weeks": [ { "sessions":[ { "subject":"", "activities":[ { "...
0
votes
1answer
15 views

JSON-B serializes Map keys using toString and not with registered Adapter

I have a JAX-RS service that returns a Map<Artifact, String> and I have registered a public class ArtifactAdapter implements JsonbAdapter<Artifact, String> which a see hit when ...
0
votes
1answer
22 views

jsonb data type lookup cost in postgres

This might be an obvious and simple question. But I read through the jsonb data type documentation, but nowhere it mentions the lookup cost of a key in jsonb data. For example, let's say I have a ...
0
votes
0answers
11 views

Dynamic JSONB key query

I have a jsonb field with this data: {"en":"english text", "es":"spanish text", "fr":"french text"} I have to make a query for find the text that match partially in the language specified by user. I ...
0
votes
0answers
25 views

Postgres jsonb document storage like mongo

I have simple table like this CREATE TABLE things ( id SERIAL PRIMARY KEY, data jsonb ); and i want to store complex nested json like this { "id": 1, "title": "thing", "...
1
vote
1answer
23 views

Accessing jsonb object in postgress throws error

I have a table called "junaid", which has a column "connections" which is of type "jsonb". create table junaid ( connection jsonb } The value in the "connections" column is array of objects. ...
0
votes
1answer
46 views

BigQuery: Best way to handle frequent schema changes?

Our BigQuery schema is heavily nested/repeated and constantly changes. For example, a new page, form, or user-info field to the website would correspond to new columns for in BigQuery. Also if we stop ...
1
vote
2answers
96 views

Select a specific entry in json with PostgreSQL

I have the following JSON which is stored in a jsonb field named "Data" in a PostgreSQL database: { "CompetitionData" : { "StartDate" : "12.06.2018", "Name" : "TestCompetition"...
0
votes
1answer
22 views

Postgres jsonb nested array append

I have simple table with a jsonb column CREATE TABLE things ( id SERIAL PRIMARY KEY, data jsonb ); with data that looks like: { "id": 1, "title": "thing", "things": [ ...
1
vote
1answer
25 views

Define @Type in orm.xml (Spring Data / JPA 2.1 /Hibernate 5.3.7/Postgresql)?

We need to perform CRUD operations on an object in Postgresql that has a jsonb field. I understand that we can create a user type and annotate them with @Type(type = "jsonb") in the model. However, we ...
0
votes
1answer
25 views

Selecting absent value from Postgres JSONB field

I have a table entries with a field details which type is jsonb and default value is '{}'::jsonb, on PostgreSQL 10.5 When I run SELECT details->foo FROM entries I get an ERROR: column "...
1
vote
1answer
42 views

Remove key from multiple nested jsonb objects with unknown keys in PostgreSQL

I have a table with a column of jsonb objects that look similar, but have different top level keys in each row. Each record looks something like this, but with different top level keys: { "10": { ...
0
votes
1answer
54 views

How to select data in spring boot using jpa based on where condition of jsonb column of postgres(without native query)?

I have Postgres database with jsonb field. I have used Predicate list for where condition with criteria builder in jpa. Now, I want to fetch JSON data stored in a database based on multiple where ...
1
vote
1answer
29 views

How to query Postgres JSONB arrays with = operator

I'm looking for a way to query postgres jsonb array field with kind of = clause. Let's assume I have a table CREATE TABLE events( id integer, tags jsonb, PRIMARY KEY(id) ); tags having ...
0
votes
2answers
54 views

Remove element from jsonb array

I have the following jsonb. From the array pages I would like to remove the element called 'pageb'. The solutions offered in similar questions are not working for me. '{ "data": { "id": "...
0
votes
1answer
105 views

How to query PostgrSQL JSONB array with Any with Dapper

I'm trying to get a query like the following done with Dapper. Documentation around json is a bit slim though and I couldn't figure it out so far. Basically I need to run a query similar to this one. ...
0
votes
0answers
10 views

How do I write a custom Rails Association?

How can I write a Rails 5.2 association for a relationship stored in jsonb? I'm using PostgreSQL jsonb in a few places to flexibly store relationships with other models. For example, we're using a ...