The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
0answers
11 views

Is JSONB the righ datatype on this case?

I have a person table where arbitrary data must be inserted. Since I'm using postgres 9.4, JSONB appears to be the right choice. Data Ex.: id: 1, name: "Joe Doe", preferences: { color: "red" , toy: ...
3
votes
2answers
119 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
0
votes
1answer
43 views

Usage of json to store multiple attributes

Typically we have the table designed like this UserId FirstName LastName EmailId MobileNumber Age Sex City Where UserId is going to be the primary key Is it good to have multiple columns ...
-2
votes
1answer
44 views

No operator matches the given name and argument type(s). You might need to add explicit type casts [closed]

postgres=# select * from json_test; id | data ----+----------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b":["C", "d"]} 4 | { "a":1, ...
2
votes
1answer
45 views

“ERROR:malformed array literal” when using json_to_record with a JSON array element in Postgres 9.4

This illustrates the issue nicely: When column b is of type text, and not an array, the following works: select * from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') as x(a int, b ...
1
vote
1answer
58 views

sql org chart hierarchical table to xml json

I have a table of employees and managers: create table emp_mgr (emp varchar(99), mgr varchar(99)) insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr ...
4
votes
3answers
58 views

Use trigger to synchronize columns with fields in json column on insert or update

I'm a bit of a database/postgres beginner,so bear with me. If I have a table, something like this. CREATE TABLE testy ( id INTEGER REFERENCES other_table, name varchar(128) PRIMARY KEY, ...
2
votes
1answer
65 views

Postgres 9.4+: JSONB instead of EAV

I'm figuring out a way to structure a new postgresql database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seens like a flexible ...
1
vote
0answers
24 views

Best way to perform real time analytics on a growing collection of json file ( very large ) [closed]

I have a very large collection of JSON file (tens of millions) on S3 and I need to have some kind of aggregation (perform continuously as the collection grows). The aggregation is mainly counter ...
1
vote
1answer
35 views

Using the value of a JSON object that is stored in a PostgreSQL JSON array

I have a table that looks like this (simplified): Jobs ------------------------------- ID | Details ------------------------------- 1 | { 'tasks': [] } 2 | { 'tasks': [{'id': '1', 'due_on': ...
1
vote
1answer
40 views

Returning case-sensitive input of case-insensitive array match

Taking a JSON table with some simple data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}'); INSERT INTO jsonthings(d) ...
0
votes
1answer
73 views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
2
votes
1answer
56 views

Postgresql: Aggregate objects into an (json) array (subquery issues)

I'm sorry for the vague title, but I simply don't know the right words to describe this. I have this query that transforms a bunch of columns into an object that works just fine: SELECT ...
1
vote
1answer
107 views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
1
vote
0answers
189 views

MySQL method for inserting data from JSON using Python Connector

I have been using Python to insert parsed data from a JSON server response into a MySQL database. The JSON data consists of around 10,000 responses, each holding around 40 nested pieces of ...
1
vote
1answer
68 views

PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

Could I create a VIEW from the JSON data type but have it as a relational structure? Example: SELECT * FROM json_test; Results [ { "name": "Roy", "Country": "USA", ...
0
votes
0answers
54 views

indexable JSON datatype in MySQL/MariaDB

In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent? I'm specifically interested in the MariaDB Galera ...
1
vote
1answer
64 views

In SQLCMD is there any way of shelling out to run arbitrary programs?

In the past I have dropped json into a table in a nvarchar(Max) column. This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit ...
0
votes
0answers
18 views

Is there a Document based nosql with deep json update?

I'm currently looking in a Document based nosql (possibly with sharding/replication/NSPOF) database that allow (is specialized in) deep json document update. By that I mean that I would like a nosql ...
1
vote
2answers
399 views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data ...
1
vote
0answers
58 views

How to set the trigger when field(contain json value) is updated?

I have one table in which there is one field called info.This field hold json value. I want to create trigger on this field.I want to split this json format string into record I mean I want to insert ...
5
votes
1answer
194 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
3
votes
1answer
376 views

PostgreSql JSONB SELECT against multiple values

I have a very simple JSON table which I populate with some sample data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}'); INSERT INTO ...
0
votes
1answer
68 views

How to store thousands of properties related to a record in PostgreSQL?

I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static ...
0
votes
0answers
87 views

PostgreSQL and array of custom type

One of the table I have looks like this CREATE TABLE orders_suppliers_contacts ( supplier_id bigint NOT NULL, name character varying(64) NOT NULL, phone character varying(18), notes character ...
1
vote
1answer
104 views

Postgresql CRUD operation on Single json data row

My requirement is that I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk. Table structure is ...
0
votes
1answer
78 views

How to know what server and port to select in MongoVue

I am unsure about which server and port to select when trying to start up MongoVue. FYI, MongoVue is the windows GUI for using mongodb (for those macusers who may want to answer this question). I have ...
1
vote
1answer
170 views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": ...
3
votes
2answers
849 views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
3
votes
1answer
383 views

PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key

If I have a table containing: CREATE TABLE test( id SERIAL PRIMARY KEY, name VARCHAR(200), age INT, data JSONB ); and data column populated with {"name": xxx, "age": yyy}, sometimes ...
0
votes
1answer
66 views

sorting by partial key name in json column

I have column document with data type json. I save here data for logging. So document has: {blocks: { participant-information_primary__Primary__39011__2288960: first_name: Firsst name, ...
0
votes
1answer
268 views

Order specific columns in json_agg

I want to sort the rows inside the json_agg function. I found this: PostgreSQL : select columns inside json_agg Which gave me a solution to selecting specific columns for json_agg, but now I want to ...
0
votes
1answer
172 views

Correct place to put very large text field for mysql/mariadb

I have an app where a queue is being processed, Each time an entry is processed the result is stuck in a separate table. The queue table gets inserts and updates, the result table inserts only. The ...
2
votes
0answers
133 views

Postgres and numeric json arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ...
0
votes
0answers
54 views

Markup Languages and SQL Queries

I need to store some metadata in an MySQL database. This data will need to be stored in some form of markup language and reside in a 'meta_data' field on a table. I will need to run searches ...
0
votes
1answer
317 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
3
votes
1answer
713 views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
2
votes
2answers
3k views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
1answer
124 views

Is there any relation between BSON storage for mongoDB and MySQL storage?

I am working on an application where I need to make analysis that what will be the storage size of BSON structure if same data is stored which is stored in MySQL. Basically I am comparing MongoDB and ...
2
votes
1answer
756 views

Fetch nested results as JSON PostgreSQL 9.2

Consider the following tables Device -------- id name type -------- components -------- id INT type VARCHAR -------- Manufacturers ------------- id INT name VARCHAR country VARCHAR ------------- ...
3
votes
0answers
398 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
4
votes
1answer
3k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
0
votes
2answers
1k views

Primary key within JSON data in Postgresql

I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key: > SELECT json->'id' AS id FROM t LIMIT 1; id ...
1
vote
1answer
909 views

How do I return JSON structured as the transpose of json_agg() in PostgreSQL?

I'm trying to convert an arbitrary subquery to JSON in PostgreSQL. json_agg() seems like the obvious tool for the job. For example: select json_agg(a) from ( select count(lead_source), ...
3
votes
1answer
1k views

PostgreSQL JSON field performance with large documents

How well is the Postgre JSON type optimized for large JSON documents? I am especially concerned about partial retrieval (e.g. the cost of getting the last N items of a JSON array or looking up one ...
7
votes
2answers
5k views

How can I update a json element in postgresql json datatype

I can't figure how can I update an element in a postgresql 9.3 datatype. My exemple the db CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender ...
4
votes
1answer
2k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
11
votes
2answers
13k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } What I would like to do is to turn that tags array into a concatenated ...
7
votes
3answers
5k views

What can I do with the new json field?

PostgreSQL 9.2 introduced the json field type. Why and when should I use it? What benefits does it have over a text field? I thought there were new query options available, however I haven't seen ...