Questions tagged [jsonb]

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

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 ...
0
votes
1answer
9 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
19 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
15 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
23 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
21 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
41 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
14 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
13 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
20 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
votes
1answer
19 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
38 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
24 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
44 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
26 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
22 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
10 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
22 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
25 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
27 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
34 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
37 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
36 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
17 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
38 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
23 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
33 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
37 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' ...
1
vote
2answers
45 views

Generate a complete JSON Object from a table with relations with other tables using Posgtres JSON functions

I want to use Postgres to generate the user JSON object from 'user', 'user_role', 'role' and 'permission' as follow: CREATE TABLE "user" ( id SERIAL PRIMARY KEY, name TEXT, ...
0
votes
2answers
61 views

Delete element in a deeply nested array in jsonb column - Postgres

I have a table my_table with a jsonb column that contains some data, for instance, in a single row, the column can contain the following data: [ { "x_id": "1", "type": "t1", "parts": [ ...
0
votes
0answers
20 views

Postgres JSONB Array Query

How can I use the jsonb query functions to get matching TrackingDetails from the whole table, when the jsonb field in my table contains json in this format? For example, I have an order_events table, ...
0
votes
0answers
16 views

Getting result in jsonb data type into view in PostgreSQL, Codeigniter

I have a table which has two columns, id(primary) and itinerary(jsonb) which the second column's data type is jsnob. My query which is shown below is working fine. SELECT itinerary FROM tbl WHERE id =...
0
votes
1answer
83 views

Speed up JSONB full text search in PostgreSQL/SQLAlchemy

The performance of my JSONB full text search is awfully slow using PostgreSQL and SQLAlchemy. How can I speed it up? Model class Book(Base): __tablename__ = "book" id = Column(Integer, ...
0
votes
1answer
23 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
62 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", ...
1
vote
2answers
49 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
28 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
24 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
28 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
28 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
23 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
40 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
45 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
17 views

Aggregate jsonb unique values in one field in postgres

I have a dataset like this: id | recipients_by_data ------------+------------------------------------------------------------ 1000000001 | {"...
1
vote
1answer
28 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
46 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 ...