The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
18 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
24 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
17 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
7 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 ...
2
votes
0answers
34 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
43 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
46 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
18 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, ...
2
votes
1answer
68 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
25 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
28 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
32 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
72 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) ...
1
vote
1answer
23 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
49 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 | ...
0
votes
1answer
59 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
57 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
38 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
37 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
57 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: ...
0
votes
1answer
38 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 ...
1
vote
1answer
34 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
97 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
122 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
108 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 --> ...
1
vote
2answers
42 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
45 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
45 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
42 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
71 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
168 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
86 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
28 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
197 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
72 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
219 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
96 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
1answer
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
40 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}) ...
1
vote
1answer
397 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
62 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
238 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
144 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
405 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 ...
1
vote
1answer
934 views

MongoDB update object in Array

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

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
0
votes
1answer
117 views

Convert two columns to json

I have a json column with a json array in the form of [{key : "key", value : "value"}, {key : "key2", value : "value2"}]  that I wish to convert to a new form {"key" : "value", "key2" : ...
1
vote
1answer
54 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
0
votes
0answers
135 views

How to generate a JSON file from stored procedure(SQL) result

I want to get the data from DB using stored procedures in the form of a JSON file. In simple words, my output should be a JSON file which should be the result of data in DB (collected by a stored ...