Binary version of the json data type, used in Postgres 9.4+. The major practical difference is efficiency.
0
votes
2answers
19 views
Postgres extract value from jsonb array
I have a jsonb field with an array like this one below:
[
{
"type":"discount",
"title":"Discount 10%"
},
{
"file":"...
1
vote
1answer
28 views
“Big” data in a JSONB column
I have a table with a metadata column (JSONB). Sometimes I run queries on this column. Example:
select * from "a" where metadata->'b'->'c' is not null
This column has always just small JSON ...
0
votes
1answer
26 views
Postgresql query for objects in nested JSONB field
I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like:
No integer,
Content jsonb
Say I put many records like:
"No": 2, {"obj":"x","Item": {"Name": "BigDog",...
0
votes
1answer
11 views
Targeting specific objects in jsonb array
I need to select rows from table that match specific criteria which involves jsonb object field comparison.
In the example below, I'd like to get only rows for which a value is within a min/max range ...
0
votes
1answer
20 views
Django / PostgresQL jsonb (JSONField) - convert select and update into one query
Versions: Django 1.10 and Postgres 9.6
I'm trying to modify a nested JSONField's key in place without a roundtrip to Python. Reason is to avoid race conditions and multiple queries overwriting the ...
0
votes
1answer
13 views
Failed Signup with Devise nested parameters model have serialized column
During a signup process I have a user model and Tenant model. Recently I added a serialized column to the Tenant model and I can update the this column fine. However when creating a new Tenant I have ...
0
votes
1answer
9 views
String Manipulation throws error while Inserting into PostgreSql Jsonb Column
I have the following code, to insert data into my table res(ID bigserial,
Results jsonb not null). I want to insert data so that 'Display' column always has the 'i' appended to it, so that every ...
0
votes
0answers
22 views
PostgreSql - Gin Index doesn't help in improving performance
I am pretty novice in the PostgreSql world.
I store the following JSON objects in the jsonb column of PostgreSQL as one object per row.
{"cid":"CID1","Display":"User One CID1","F-Name":"Craig","LName"...
2
votes
1answer
77 views
Postgres - Performance of large jsonb column
We are using Postgres jsonb type in one of our DB tables. Table structure is shown as below:
CREATE TABLE T (
id UUID NOT NULL PRIMARY KEY,
payload JSONB
);
CREATE INDEX ON T USING gin (payload ...
1
vote
1answer
38 views
Select objects inside array JSONB
I am attempting to grab the objects within an array using JSONB in a Postgres db and Groovy.
My Json is structured like this, stored in my database.
"playersContainer": {
"players": [
...
0
votes
0answers
15 views
PostgreSql - Determining best Index on jsonb column
I am pretty novice in the PostgreSql world.
I store the following JSON objects in the jsonb column of PostgreSQL as one object per row.
{"cid":"CID1","Display":"User One CID1","F-Name":"Craig","LName"...
1
vote
0answers
26 views
Streaming data into complex Json in PostgreSql
I have the following Json stored in the Jsonb column of PostgreSql. I have a requirement where I should be able to update the Json as and when the streaming of data occurs.
{
"root": [
[{
...
0
votes
0answers
24 views
Querying postgresql using sequelize ORM for an nested JSONB object
This is how the JSONB looks into the database
matrix:{
toolsMethodsOther:{
networking: [],
testing: {
testingGeneric: [],
testingTools: []
},
developmentTools: [],
...
0
votes
1answer
31 views
Filtering on PostgreSQL JSONB column doesn't work
I have the following JSON stored in column of type jsonb. I want to query the second element of the outer array and filter all rows that have the value in 'cid' column as 'CID1'.
{"root":[[
...
0
votes
1answer
21 views
postgres: why does this GIN index not used for this “object in array” query
I am trying to index a JSONB column that contains array of objects :
create table tmp_t (a INTEGER PRIMARY KEY,o jsonb);
insert into tmp_t (a,o) values(1, '[{"frame": 1, "accession": "NM_001184642....
0
votes
0answers
21 views
Postgresql gin index high cpu usage ratio
I design a table with jsonb column to store userlist, and I set up a gin index on that column.
select * from table where userlist @> '"Username":"a01"'::josnb
My data type in table like
`[{"...
-4
votes
1answer
41 views
Storing JSON Array of Arrays in PostgreSql
I have the JSON in the following format stored in Postgres JsonB Column:
[
[
{"cid":"CID1","Display":"User One","FName":"User","LName":"One"},
{"cid":"CID1","Display":"...
0
votes
1answer
32 views
PostgreSQL many jsonb columns vs many rows
From other answers on many columns vs many rows (or tables) it seems columns are more performant for normalized data. What about serialized data?
I'm going to store many in-progress web forms, i.e. ...
1
vote
1answer
37 views
JSR-367: How to bind a simple json to object and extract data
How to bind the json file to object.
Here I am using JSR-367 API , yasson implementation to implement.
The JSON file looks like this
{
"Details": [
{
"age": 27,
"gender": "Male",
...
0
votes
0answers
21 views
Store base64 image in jsonb
I'm using jsonb columns to store entity data with variable structure, it works great. Now I have some images in this variable structures let's say:
{
thing: {
subthing: {
id: 'abcdef-...
-2
votes
0answers
17 views
How to get the size of a JSONB array in Postgres?
How can I get the size of an array within a JSONB array in Postgres 9.6?
select * from mytable where myjson->'a'->>'b' is not null;
This shows all the rows where myjson.a.b isn't null, but ...
0
votes
1answer
13 views
Query rows for matching JSONB column where key ends with a name and key value is a specific value
Given the following rows with a jsonb column details. How do I write a query so that records where the key name ends with _col with value B are selected. So records with ids 1, 2.
id | details
1 | ...
1
vote
1answer
25 views
PostgreSQL difficulty extracting field from jsonb column
Using PostgreSQL 9.5.5
Given the below example jsonb data in a column:
{
"item_id": "123456",
"action_information_1": [ {"value": "259", "action_type": "read"} ],
"action_information_2": [ {"value"...
0
votes
1answer
38 views
JSONB query in Rails for a key that contains an array of hashes
I have a Rails 5 project with a Page model that has a JSONB column content. So the structure looks like this (reduced to the bare minimum for the question):
#<Page id: 46, content: {..., "media" =...
2
votes
1answer
27 views
Querying JSONB using Postgres
I am attempting to get an element in my JSON with a query.
I am using Groovy, Postgres 9.4 and JSONB.
Here is my JSON
{
"id": "${ID}",
"team": {
"id": "123",
"name": "Shire ...
1
vote
2answers
29 views
How should I upgrade from bradjasper's django-jsonfield to Django's built-in jsonfield?
I have a Postgres 9.4 / Django 1.8 database that uses bradjasper's django-jsonfield package. (See https://github.com/bradjasper/django-jsonfield ) It works well, but I would like to upgrade the ...
0
votes
0answers
38 views
Postgresql 9.5 Performance degradation when updating JSON field using jsonb_set
I have a performance problem when using jsonb_set to replace values of a jsonb field.
It seems that every time the values are replaced in the JSONB expression the time updating the field increases.
...
1
vote
1answer
35 views
Postgresql - distinct values from jsonb array
I have the following jsonb array column (tags)
|name | tags (jsonb) |
--------------------------------
|john | [ "foo", "bar" ]
|smith| [ "bar", "bat" ]
|adam | [ "foo", "dot" ]
How to get the ...
1
vote
1answer
28 views
PostgreSQL - is jsonb_set() performance O(1)?
I am building a social network, and trying to implement a data structure for saving time line event ids for each user. I was inspired by Redis Twitter Clone and its use of Redis Lists.
So for ...
0
votes
0answers
28 views
comparing 2 values of json array element in where clause
Here is my scenario. I have my application installed on a computer. I have an environment variable which tells me which drive the application is installed. I also have information about each drive ...
0
votes
1answer
25 views
PostgreSQL json(b) - Convert string to array & update field
I have a jsonb field in PostgreSQL with the following content:
{ "object": { "urls": "A;B;C" } }
What I want to do is update the value of urls inside the object and transform the string with ...
0
votes
0answers
36 views
Issue with Sqlalchemy and inserting array of jsonb to postgresql
So i'm trying to insert an array of jsonb values into my database but I can't seem to format it right, here's my code:
updated_old_passwords.append({"index": 1, "password": hashed_password})
user....
0
votes
1answer
66 views
How to COPY CSV as JSON fields
Is there a way to COPY the CSV file data directly into a JSON or JSONb array?
Example:
CREATE TABLE mytable (
id serial PRIMARY KEY,
info jSONb -- or JSON
);
COPY mytable(info) FROM '/tmp/...
1
vote
2answers
16 views
Return element of jsonb field with activerecord select
How do I 'select' a value from within a jsonb stored field?
e.g.
@model1:
data: {"special_date" => "Wed, 16 Mar 2016 11:20:20 -0700", ....}
@model2:
data: {"special_date" => "Wed, 23 Mar ...
0
votes
0answers
37 views
Find and update a collection in JSONB
I have a Rails 5.0 app with a JSONB column called data, which contains an array of hashes:
[
{'event': 'web_session', 'user_id': 1, 'count': 13},
{'event': 'web_session', 'user_id': 2, 'count'...
0
votes
1answer
29 views
How to loop through jsonb array of objects to get values of keys within objects Postgres
I am using Postges 9.4 and I have a table that has a jsonb field as an array of objects.
Here is a sample field:
[{"pk": 224, "arbitrary_value": 50, "description": "United States"}, {"pk": 125, "...
1
vote
1answer
28 views
How to retrieve messages from a given date using Jsonb?
I have the following table:
CREATE TABLE tbl (tbl_id int, messages jsonb);
CREATE INDEX index_tbl ON tbl USING gin (messages);
And the JSON messages as array:
[{"user_id":1,"created_at":"2016-12-20"...
4
votes
2answers
65 views
What is the best alternative for the following situation?
I'm using a JSONB field in my Postgresql database to store the following document. I own thousands of documents. I need to create reports with this data, but the search is very slow.
If I need to ...
2
votes
2answers
62 views
How to find the minimum value in a postgres sql column which contains jsonb data?
I have a table t in postgres database. It has a column data which contains jsonb data in the following format (for each record)-
{
"20161214": {"4": ["3-14", "5-16", "642"], "9": ["3-10", "5-10", "...
1
vote
1answer
41 views
How to find the key for the minimum value in jsonb column of postgres?
I need to find the key of the minimum value in a jsonb object,I have found out minimum value, need to find the key of the same in the same query.
Query I am using
SELECT id,min((arr ->> 2)::...
1
vote
1answer
99 views
Postgres coalesce to empty JSONB array
How can I coalesce a null column into an empty JSONB array? This doesn't work:
SELECT jsonb_array_elements(coalesce(null_column, '{}'::jsonb))
FROM table
WHERE id = 13;
-- ERROR: cannot extract ...
0
votes
1answer
65 views
JPA native query mapping POJO class with JSONB value on PostgreSQL
I was trying to retrieve joined values from multiple tables into a custom POJO using a native query. One of the values I want to retrieve is a JSONB field. While I'm able to get the entity with that ...
0
votes
2answers
46 views
How can I get the distinct values of all columns in a single table in Postgres?
Ideally I'd like to run a single query that returns a table where each row is a column name of a specified table, and jsonb array of all the distinct values in the table corresponding to that column.
...
0
votes
1answer
37 views
PostgreSQL jsonb nested pattern matching text search across multiple rows
as stated in the title, I am attempting to do a query that preforms a full text search on multiple rows, agains a jsonb data type, with nested data, the problem is as follows:
CREATE TABLE books (id ...
1
vote
0answers
29 views
Postgresql JSON index strange query time
Let's say we have table like this:
CREATE TABLE user_device_infos
(
id integer NOT NULL DEFAULT nextval('user_device_infos_id_seq1'::regclass),
user_id integer,
data jsonb,
created_at ...
1
vote
1answer
24 views
sorting data by “value” from jsonb_each(), is reliable?
Given jsonb type column and data structure like this:
{
"1": 10000.2,
"2": 77.2,
"3": -200.09,
"4": 12.55
}
Need retrieve result from this json and sort by VALUE
SELECT * FROM jsonb_each(
...
1
vote
0answers
65 views
Improve ranking times on multiple JSONB fields search in PostgreSQL
My search times are actually quite fast now but as soon as I start to rank them for the best results I hit a wall. The more hits I get, the slower it gets. For uncommon terms the search takes ~2ms and ...
1
vote
1answer
33 views
Index for ranking JSONB search results in PostgreSQL
I'm currently optimizing my search results on jsonb fields of PostgreSQL. I'm using Postgres 9.6. My ultimate goal is to search on multiple fields within my jsonb document and rank the results ...
4
votes
0answers
41 views
Why are Postgres lookups on jsonb columns so slow?
I have a table targeting that has a column marital_status of type text[] and another column data of type jsonb. The content of these two columns is the same, just in a different format (it's just for ...
1
vote
0answers
31 views
Java json string to Serializable [duplicate]
I'm meeting this problem.
I'm using PostgreSQL with a column whose type is jsonb in a table. After Hibernate mapping, it come to Serializable. I got one json string from client, and want to store ...
