Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [json]

JavaScript Object Notation (JSON) is an open, human and machine-readable standard that facilitates data interchange, and along with XML is the main format for data interchange used on the modern web.

69 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
1answer
68 views

How to “insert into select from” skipping exceptions

1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle. I will need to report on JSON ...
1
vote
1answer
16 views

Build a single JSON value from two jsonb columns in two different tables

I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id. I am trying to build one aggregated JSON value from these jsonb columns. Lets say table A ...
1
vote
0answers
152 views

How to Use LOAD DATA to import JSON data and specify date data type on import

I'm importing JSON data in sql and would like like to convert human readable date data to yyyy-mm-dd format. Here's the flow: I'm using the following json file stored under /home/name/json_data/...
1
vote
1answer
377 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
1
vote
0answers
40 views

How to read jsonb or json in postgresql spi?

How can I read a json/jsonb field in a PostgreSQL server side function? I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain ...
1
vote
0answers
24 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
1
vote
0answers
89 views

Get JSON with where condition mysql

I have JSON value in column : INSERT INTO `bt_product` (`_Price`) VALUES ('{\"245\":\"10\",\"366\":\"5\",\"688\":\"1\"}'), ('{\"26\":\"1\"}'), ('{\"32\":\"1\"}'), ('{\"52\":\"1\"}'), ('{\"42\":\"1\"}'...
1
vote
0answers
39 views

Simplifying database structure with many unequal vectors

I want to find an efficient way to represent the JSON objects below. I made an ERD diagram to represents the JSON objects. This diagram below is a small piece within a much larger structure. There ...
1
vote
2answers
238 views

Indexed range comparison of array key's value in row inside JSONB

This is for tests with results. The order and if the test was done can be vary per row. column jdata {"name": "Somedata", "array": [ {"name":"test0","value":4.6}, {"name":"test2", "...
1
vote
1answer
117 views

Store and search leveled categories with mysql json

I have leveled category triplets column, that tag the specific blacksmith with items he can produce: blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int) ...
1
vote
0answers
260 views

Export json data into single cell

My problem is a relatively simple one; unfortunately, I haven't been able to find any solutions from searching around. I have a SQL table called alerts from which I'm trying to export data into CSV ...
1
vote
1answer
569 views

How to detect JSON data type support in MariaDB/MySQL

My first thought was to check server version but it is not the nicest solution. Is there something like PHP's function_exists() in mysql? I'd like to detect JSON_EXTRACT() or the JSON data type: ...
1
vote
0answers
86 views

How do read queries against Postgres's JSONB type compare to Couchbase?

I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large ...
1
vote
0answers
106 views

Is it possible to return empty response if there is no records in DB

Is it possible to return empty objects if there is no records in db? db.query(' SELECT * from users where type = $1 and category = $2 limit 8', [admin, filltime], function(err, result) { ...
1
vote
1answer
45 views

Postgres - detect change in JSONB column value from 'null' to some value across rows

I have to work with a table that looks like this: The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } ...
1
vote
0answers
83 views

What data store should I use for storing and querying a billion small (~2kb) JSON documents?

I'm trying to figure out which database to use for storing and querying on ~1 billion JSON documents about the size of 2KB each. About 2 TB of data. Nature of JSON documents: 3 types of documents (...
1
vote
0answers
1k views

How do you concatenate JSONB columns in a PostgreSQL query to produce a JSONB object?

I am trying to produce a specific json object output from rows of a single column of jsonb and I'm stuck. Before I drop into PL/pgSQL, I wanted to ask the question here. I need to concatenate a ...
1
vote
0answers
12 views

JSON format for time-logging

Hi I'm creating a very simple application that logs activities. Right now I'm thinking I would just do {"Activity":[ {"ActivityType":"Sleep"}, {"time_begin": someunixtime, "time_end":...
1
vote
0answers
122 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 ...
1
vote
0answers
653 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 ...
0
votes
0answers
12 views

Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items ...
0
votes
0answers
22 views

BigQuery - export JSON with null columns

I have table in BigQuery with structured data - hence to export it I need to use JSON format. Default BQ export to JSON behavior (not to say "a bug") is to omit all columns with null values. But in my ...
0
votes
0answers
31 views

Advantages and disadvantages of JSONB columns

I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense. For example, we store a "seen by" state in our messages table and need to use ...
0
votes
1answer
28 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
1answer
28 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
0
votes
0answers
94 views

Is there any way to query postgres JSON/JSONB columns with ilike and indexing?

I have a table called user_data where I have JSON column called data which can have any key that can be given by application users. Here I wanted to do ilike query with using proper index. Since keys ...
0
votes
0answers
26 views

Converting User-Defined Datatypes to JSON in Oracle

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application: CREATE OR REPLACE TYPE udf_typ AS OBJECT ( name VARCHAR2(4000), value VARCHAR2(...
0
votes
0answers
145 views

How to control how JSON_ARRAYAGG orders results

I am testing MySQL JSON_ARRAY and JSON_ARRAYAGG functions in order to move some application expensive processes to SQL stored functions. In the following e-commerce application example, I am trying ...
0
votes
0answers
16 views

Database design for arrays - utilize relational database

I am trying to build an application, where users can create unlimited "parsing fields", that will contain either a: String Array id | name | type ------------------- 1 | name | string 2 | table |...
0
votes
1answer
35 views

Append JSON array to an object in PostgreSQL

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. +----+------------+----------+----...
0
votes
0answers
38 views

How to get json object from json array where one of it's value fulfill assumption

In mysql I have json array like this [{"amount": "53.00", "paid_at": 2019-05-10, "payment_date": "2019-05-16"}, {"amount": "53.00", "paid_at": false, "payment_date": "2019-06-16"}, {"amount": "53.00"...
0
votes
0answers
29 views

What is the best way to update a table from a JSON string?

I'm fiddling around with JSON and SQL, and I need to update a record based on a new JSON string. Here's what I've got, but to be honest, It seems like an awful lot of code: create procedure ...
0
votes
0answers
30 views

What is the proper way to index a column in MySQL that has a lot of json data in it?

We have a long row that includes a json column that holds variable columns specific to each row. We currently do not index that column as we don't query against it - rather it is supporting data for ...
0
votes
1answer
50 views

How to filter a value in json field type on Postgres 9.2?

My json field data is like this: {"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
0
votes
0answers
11 views

how to get data from multi table with column json in postgresql?

I have a lot of tables like the following tables. main_table id |customer_id | tabl2_id | tabl1_subJson_id |... ---|------------|-----------|------------------|------ 1 | 1 | 1 | ...
0
votes
0answers
38 views

Select / remove nested JSON attributes postgresql

I have the following JSON structure in postgres. drop table if exists data cascade; create table json_test (data json); insert into json_test values ('{ "a": "1", "b": "2", "c": "3", "level2"...
0
votes
1answer
31 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
0
votes
1answer
156 views

How to unnest and display elements of a JSON array?

i have this table : And i want display all the names in the table like this : Also i want to list the number of gender each genre : Desired output:
0
votes
1answer
13 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
0
votes
1answer
36 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...
0
votes
0answers
61 views

Select JSON elements from nested documnet

I have a JSON document such as: { "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } } Here is the table definition: CREATE TABLE "v22"."assets_exp" ( "_id" ...
0
votes
1answer
59 views

Storing data as JSON vs meta table

I am using MySQL 5.7.26 and have a very large table with multiple foreign keys for a column. I can store it with JSON type in a single table - tours (Case A) or by creating another table - like metas (...
0
votes
0answers
98 views

PostgreSQL: join by dynamic nested JSONB attribute

I'm having quite an unusual task and I'm not even sure if it's solvable. I have a table with JSONB column which can contain arbitrary nested JSON objects. Let's call it objects table. And I have a ...
0
votes
0answers
27 views

Map PostgreSQL JSON to view table

I have the following table with some JSON data | id | from | to | |-----|:------------------------...
0
votes
1answer
107 views

Multi-master (NoSQL) database solutions with cloud and no-premise option

We are currently investigating database options for distributed database (possibly globally distributed) where the data in general is stored in "cloud" (this would not be a special requirement). The ...
0
votes
0answers
12 views

How to always select records in array or empty array

I have a field records of jsonb[] type and I need to insert records from other tables(has many relation) whenever there is an update via trigger. When I apply select SELECT INTO records * FROM "...
0
votes
0answers
47 views

PostgresSQL update specific property in specific object inside an array

Ehi all :) Using the postgresql as document store, but now I'm having some issues on setting a specific property. This is my json. { "Id": "1d8a9c43-5ece-44f9-b468-c1c87cdef47a", "Input": { ...
0
votes
0answers
25 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
0
votes
0answers
37 views

How to model data in DB (PostgreSQL) for SNMP-like multiple configurations

I am working on product managing and monitoring Network (NMS-like products). Product manages configuration of network devices, for now each device has stored its configuration in simple table - this ...
0
votes
1answer
475 views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...