The tag has no usage guidance.

learn more… | top users | synonyms

3
votes
1answer
29 views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
2
votes
1answer
23 views

Out of range errors while with insert statements

I'm having an issue regarding every third insert line of the database I'm trying to create. Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, ...
2
votes
1answer
25 views

Require JSON to have an element

When working with the json datatype, is there a way to ensure the input JSON must have an element? I don't mean primary, I want the JSON that gets inserted to at least have the id, name element. It ...
1
vote
0answers
18 views

Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

I have two tables: hub and huboperator hub table is defined as CREATE TABLE public.hub ( parent_hub_id integer, ancestors jsonb, descendants jsonb, name character varying(1024), id integer ...
0
votes
0answers
39 views

PostgresSql - Flattening nested JSON arrays

Interesting problem trying to query nested arrays stored in JSON... Context I'm using PostGreSQL and Heroku Dataclips to navigate some data I'm pulling from a server. After doing some reading, I ...
1
vote
1answer
79 views

Consume Rest Api with SSIS

I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source. Is there a non ...
0
votes
0answers
18 views

Reading massive (disconnected)json into dynamodb

I received these massive log files, which I am currently trying to understand, and eventually put into dynamodb or redshift for analysis. These are raw file, but contain json objects. I have 2 ...
0
votes
0answers
15 views

Export data from Hive to Postgres

I would like to save data that I fetch via Hive on a remote Hadoop cluster to my local machine in a Postgres database. I am currently downloading the data as files but I have no idea on how to ...
3
votes
0answers
46 views

How should I use DynamoDB table

I have a list of items. Each item is attached to several urls and each url has a score number. So I have something like this : How should I organise this in dynamoDB? How should I format my Json ...
1
vote
1answer
56 views

See if an JSON array in MySQL contains an object whose key holds a specific date

i'm trying to find out if there is a row which contains a specific date inside a JSON array Let's say my data looks like this: Table applications: id | application_id | data # Rows 1 | 1 | [{"...
0
votes
1answer
59 views

Effectively searching through entire 1 level nested JSONB

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
1
vote
1answer
23 views

PostgreSQL 9.3: Aggregate collection of JSON arrays

I have a table inventory with a value column that contains JSON strings, with the following data structure: { "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15]...
2
votes
1answer
89 views

Aggregate count over variable number of json keys

How can one get a list of json objects like this: [ {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2}, {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1} ] from ...
0
votes
0answers
35 views

Db2 JSON enablement

I followed the instructions to setup the JSON capability on my DB210.5 Windows server, running db2nosql... -setup enable. It succeeded, but when I launch the db2nosql I see this: nosql>Type your ...
3
votes
1answer
34 views

Store info in a column as JSON

I'm learning SQL Server 2016, I learned how to extract JSON from tables. Now I need to store JSON in a column. My JSON was: {"endereco":"R. Nome da Rua, 000", "cep":"00000-000"} I`m using nvarchar ...
3
votes
1answer
42 views

Extract Multidimensional JSON

I have some tables: teste // id, descricao teste2 // id, descricao relacao // id, idTeste, idTeste2 With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to ...
0
votes
1answer
75 views

mysql 5.7 json filter and features products

sorry for my english! I need use this column(feature) for show features, filters for products and in some language(It depends on the country). I have products table(innodb) and a column(json) features,...
1
vote
1answer
40 views

Set a default value for a jsonb property in PostgreSQL

Let's say that I have a jsonb column with documents like: { "lead": "The lead", "video": "An URL to the video" } In some cases I also need to store the name of a picture that's uploaded to ...
2
votes
1answer
61 views

Filter rows on data saved as JSON arrays

My data is saved as _________________________________ id | category | other data etc -------------------------------- 1 | ["2","3"] |bla bla -------------------------------- 2 | ["254","34"]|...
0
votes
1answer
99 views

How to insert values into a table with default values from a select query in PostgreSQL?

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null? In my case, the SELECT statement is selecting from JSON. In ...
1
vote
1answer
58 views

Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

We are extracting content sections from word documents and have been going back-and-forth on how to store this arbitrary data. We are using PostgreSQL and our plan was to use JSON fields to save ...
3
votes
1answer
47 views

Does updating a jsonb column trigger updates on all expression indexes on that column?

I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column): UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123 ...
0
votes
1answer
39 views

Trigger with multiple inserts based on JSON data

I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example): -- table filled from external source, -- with only a little possible impact on ...
1
vote
1answer
63 views

Create dynamic WHERE clause from a JSON field

We have tables like this: people_people ( "id" integer... "attributes" HSTORE ) object_permission ( "permissions" JSON ) An example of an object_permission.permissions is: {"matricule":{"...
0
votes
1answer
42 views

How to return value for key from a jsonb column?

I've successfully created the below table on 9.5.1.0: CREATE TABLE json_test ( id serial primary key, data jsonb ); and successfully inserted this record: INSERT INTO json_test (data)...
1
vote
1answer
44 views

Postgres: querying jsonb array with an array

My products table has a jsonb column called 'specs'. This specs hash will often have an array field, let's call it 'spec_options'. When querying this table, I need to find any product that has ...
4
votes
1answer
98 views

Are operators also considered to be keys?

I'm taking a MongoDB course and one of the assignments asks to include quotes around keys. So I did for a query like this {$push: {"key":value}} But I was told that $push is also a key because it's ...
3
votes
1answer
171 views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups. Given this example table: CREATE TABLE grp(d JSONB NOT NULL); INSERT INTO ...
1
vote
1answer
27 views

How Can I get multi-Dimensional array by value

In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this: [{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}] I want to get when id is equal to 1. ...
0
votes
1answer
112 views

JSON MySQL 5.7 query

I have innodb table with a column in JSON format My table products: id_products int name_products varchar --> fx-6350<br> feature_products json --> {"cache":{"l1":"128k","l2":"...
1
vote
2answers
51 views

PostgreSQL JSONB: How to get an element from Array of Embedded Documents

I am comparing MongoDB with PostgreSQL query abilities. I struggle a bit with finding simple equivalent for the following MongoDB queries. What are the equivalent commands in PostgreSQL JSONB to ...
0
votes
0answers
57 views

How to dynamic aliasing field name when transport json to recordset using jsonb_to_recordset function

I've simple table like this : CREATE TABLE "public"."activity_log" ( "activity_id" int8 NOT NULL DEFAULT nextval('activity_log_activity_id_seq'::regclass), "app_name" varchar(100) NOT NULL ...
1
vote
1answer
49 views

Multiple to_json(array_agg), separate joins

I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads. So I'm shooting for messages = [{id, subject, body, ...
3
votes
1answer
47 views

Can SQL Server 2016 validate JSON on insert?

Currently SQL Server 2016 CTP 3 and later releases support NVARCHAR(max) fields with JSON content. However since the type NVARCHAR(max) is totally free-form, there is no constraint on these fields. ...
1
vote
1answer
117 views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
1
vote
1answer
222 views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count ...
2
votes
1answer
111 views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
0
votes
0answers
30 views

Does JSONB column type in export look identical to JSON?

In other words, does it export as text or as a binary blob. I'm running pg_dump. I'd love to see the text version of the JSON data, not some kind of encoding of a binary blob.
1
vote
1answer
234 views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get ...
0
votes
0answers
88 views

Migrate json file to Postgres database

Is there any easy way to migrate json file to Postgres database? I googled, but it seems to me that there in no psql command to load json data, like there is for example \copy for csv.
0
votes
1answer
262 views

How to combine concatenate operator on JSON values with AS

I'm trying to combine multiple JSON objects into one big object in Postgres. I could do this with an extra SELECT row_to_json (as in my first example), but this way it creates a extra object, which ...
1
vote
1answer
116 views

Postgresql: Optimize finding all distinct JSON keys from a colulmn

I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited ...
2
votes
2answers
1k views

Is it possible to store and query JSON in SQLite?

I need to store JSON objects in a SQLite database, and then do complex queries on it. I did a table like this: +--------------------------------------+ |document | property | string | number| +----...
0
votes
1answer
42 views

JSON to BSON in mongoDB

I have a little bit confusion how the json data internally organized (physical storage structure for dynamic schema objects) db.some_collection.insert({x:1,y:4,z:34}) db.some_collection.insert({p:1,q:...
1
vote
1answer
490 views

Postgres function to merge two json objects with overlapping keys into one object

I've got the following json object: { "a" : { "0" : 2, "1" : 4, "3" : 6, } "b" : { "2" : 8, "1" : 10, /*note this key exists in "a" too*/ "...
0
votes
1answer
63 views

JSON array as text to join in Postgres?

I have a table t1 with a TEXT column (t1textcolumn) that looks like: [ {"id":"x.f","bar":""}, {"id":"f.foo","bar":""}, {"id":"f.bar","bar":""} ] This array's "id" elements have a relation to ...
3
votes
2answers
267 views

Can I find all JSON scalars in PostgreSQL 9.3?

We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see ...
3
votes
1answer
158 views

Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X?

I have downloaded and installed SQL Server 2016 Community Technology Preview 2.3, which if I understand correctly, should contain a built-in function called JSON_VALUE. (Update: CTP 2.4 has shipped ...
0
votes
1answer
482 views

Unicode error with \u0000 on COPY of large JSON file into Postgres

I'm using Postgres 9.4 on Ubuntu 14.04. I have a 30GB JSON file that I'm trying to COPY into Postgres. But I keep getting the following error: COPY <table>(comment_jsonb) FROM '<json file&...
1
vote
1answer
1k views

MongoDB update object in Array

I have the following document: { "name" : "student_1", "courses" : [ { "name" : "Algebra", "grades" : [ 98, 96, 92 ] }, { "name" : "...