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.
667
questions
-1
votes
0
answers
12
views
How we can generate database schema from json file
I have a database dump given in json format. I need to write the database schema for it.How we can generate relational database schema from json file? The text fields are straightforward; but I am ...
- 1
0
votes
1
answer
16
views
Maria DB Managing JSON
I have a field in a MariaDB database with the following information:
{"Portal Content":{"Colours":
{"Primary":"String","Secondary":"String",&...
0
votes
1
answer
48
views
How do I convert a varchar of nested json to jsonb, then flatten it?
Apache Hop doesn't have rich json processing, so I'm pushing the data to postgres to do the job
With a deeply nested (subset example below) blob of json, how can I flatten, then store the result? I'll ...
- 103
0
votes
1
answer
16
views
How to use PostgreSQL JSON containment to match sub-item without explicitly including the entire hierarchy?
In the PostgreSQL documentation on the JSON "@>" containment operator at https://www.postgresql.org/docs/13/datatype-json.html#JSON-CONTAINMENT, it states:
Because JSON containment is ...
- 133
1
vote
1
answer
39
views
Issue with unescaped quotes in JSON output from MySQL query
I encountered an issue where the quotes in the JSON output from a MySQL query are not being escaped. I executed the following command to extract data from a WordPress database and save it into a file:
...
- 375
0
votes
1
answer
41
views
JSON_Value problem when adding persisted Computed column
i want to add a persisted column and i get an error:
Msg 8116, Level 16, State 8, Line 3
Argument data type NULL is invalid for argument 2 of JSON_QUERY function.
here is my script:
ALTER TABLE [...
- 47
0
votes
1
answer
43
views
Json or separate column?
I'm developing a table structure to store the physical activity data and users can save their progress. The activities belong to multiple programs. like beginner program suppose be like having 1000 ...
- 101
0
votes
1
answer
56
views
When using JSONb for a complex hierarchy of references, is it better to Join by aggregating the IDs in the JSON, or have additional relations table?
Lets say we have several primary tables in our database (a, b and c), and then another (x) that stores a complex, only semi-predictable JSONb object that has references to all the primary tables. In ...
- 213
0
votes
1
answer
18
views
Oracle returns invalid number of rows on json_table
When I run following query on Oracle 19, it returns 27 record instead of 30, and I cannot understand why, as I am expecting 30 records:
select *
from json_table('{"values":[[1,120674124,...
- 189
1
vote
1
answer
105
views
Sql Server, OPENJSON sql:identity() parse problems
I'm starting to work with the JSON operators in Sql Server 2019, and on an msdn page, I found a chunk talking about the $.sql:identity() operator and how that's supposed to give you an integer index ...
- 235
0
votes
1
answer
44
views
Does NoSQL databases have a common language like SQL?
I am trying to learn NoSQL databases with a focus on the JSON/key-value type. According to this blog from redis
Column, graph, key-value, in-memory, and document are all different
types of NoSQL ...
- 3,540
1
vote
1
answer
43
views
MariaDB: inconsistent SELECT results involving JSON
When executing the following statments on MariaDB 10.6.14
SET @json_1 = JSON_OBJECT('id', 'name');
SET @json_result_1 = JSON_OBJECT('test', @json_1);
SELECT @json_result_1;
SET @json_result_2 = ...
-1
votes
3
answers
115
views
Should i care about field names in JSON datatypes?
In DBMSes like MySQL, MariaDB, PostgreSQL and MongoDB, data is saved as BSON, JSON or JSONB for JSON columns or document.
All of these data types use space for key names in JSON.
Does internal data on ...
- 111
0
votes
1
answer
87
views
How do you sum **sub-elements** of a JSON field?
In PostgreSQL, how do you sum sub-elements of a JSON field?
Given: Customer <--- Order
where Order has a JSON field quantities_per_product that contains the qty and shipped qty for each product of ...
- 153
0
votes
0
answers
58
views
Azure Synapse query to multiple JSON files in data lake, parse data into table format
I want to query RAW telemetry data stored in JSON format in my data lake. I am using Azure Synapse to query the files.
I can successfully pull the data I want into a result table, but I struggle to ...
- 193
4
votes
1
answer
273
views
Add items to JSON
I have the following JSONs which I would like to combine. In my JSON I have the part 'kenmerken' where I would like to add some more items to. I thought this could be done by using the JSON_MODIFY.
...
- 2,257
0
votes
1
answer
158
views
Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL
I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
- 1
0
votes
1
answer
80
views
Selecting child items from another table as a JSON array column (without ruining performance)
Let's say I have these two tables: author(id, name, country) and publication(id, name, year, author_id).
What I want to get in the end is:
author_id | author_name | author_country | ...
- 199
2
votes
1
answer
285
views
Why does is JSON_EXTRACT's return value treated differently depending on the operator?
I'm using MySQL 8.0 (inside Azure Database for MySQL Server).
I've noticed some strange behavior while querying with the "json_extract" functionality. In the example below, I am querying a ...
- 123
4
votes
2
answers
280
views
Create JSON elements based on table content
With the following database schema, I am trying to retrieve a JSON of the items combined with properties and an indicator if they are connected.
I started with:
DECLARE @Item TABLE
(
ItemId INT,
...
- 2,257
0
votes
1
answer
58
views
Compressing a json-shaped postgres response during transport
We have an application that has a complex sql code generation scheme, that outputs queries of the form:
SELECT
coalesce(json_agg("foo"), '[]') AS "foo"
FROM
(
SELECT
...
- 471
0
votes
1
answer
127
views
Join 1 row out of multiple 1:1 tables and return as array of json objects
I have a database structure like so (in Postgres):
An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types ...
- 1
0
votes
0
answers
92
views
Recursive Breadth Traversal Query (with max depth), Returning Nested JSON, Filtering the Initial Seed
Since there wasn't much activity here, I've also asked this question on regular StackOverflow. If you happen to successfully answer here, do paste it there so you get more points.
In my DB, I have ...
- 121
0
votes
1
answer
136
views
json_merge_preserve on a column of JSON data
json_merge_preserve takes two or more JSON documents and merges them into one. However the function definition seems to only accept a fixed number of arguments.
So if I have a table like:
create table ...
- 207
0
votes
0
answers
175
views
Comparing JSON Arrays with MySQL 8
I have a Table in the form of:
Id (int)
Tags (JSON)
1
[1, 2, 3]
2
[1, 4, 5]
3
[1, 3]
I would like to construct a SELECT statement where I can compare the Array stored in the column Tags with ...
- 121
2
votes
1
answer
247
views
json_array_elements returns empty dataset in postgres
I was trying to read elements from Json array inside another array that is stored on Postgres database, but my result set was always empty.
My table looks like below:
Sample Json from table:
{"...
- 23
0
votes
1
answer
1k
views
How to parse JSON without key name in SQL Server
I have JSON values like following in a column. I tried parsing it using OPENJSON.
DECLARE @json NVARCHAR(MAX)='[[108,290,1388,2056],[108,290,1388,2057]]'
DECLARE @json2 NVARCHAR(MAX)='{"1":...
- 186
1
vote
1
answer
544
views
Return Subquery with Multiple Columns as JSON Array of JSON Objects
In my setup, the relevant tables for this problem are:
Items
Users
Collections (of Items) — each user can have multiple
Collection Items — where I track what items are in each collection.
The items ...
- 121
2
votes
1
answer
149
views
How to match data against a rules table
I have data and a set of rules in the database. I'd like to check which rules does this data satisfy. My data looks like this in JSON notation:
{
"items": [{"id": 1, "amount&...
- 23
1
vote
1
answer
33
views
Checking if a specific date is closed in a JSON data stored within a text field in MariaDB
Problem Description
I'm currently working on a complex stored procedure that involves a table called "price_options_test". Within this table, there is a text field named "close_days&...
- 13
0
votes
1
answer
52
views
How to calculate quantity based on a boolean field and retrieve values from a JSON field in MySQL
I'm working with a table called price_options that has the following fields: id, slots, list_slots, and quantity.
Issue Description
My objective is to calculate the quantity for each price option ...
- 13
2
votes
1
answer
551
views
Detect keys whose values are objects or arrays automatically in SQL JSON Query
I have these JSON:
Application 1:
{
"business_industry": "Agriculture",
"docs": [
{
"internal": false,
"type": "Asset & ...
- 135
1
vote
1
answer
500
views
Querying JSON key with values as JSON in SQL Server
I have these JSON:
Application 1:
{
"business_industry": "Agriculture",
"docs": [
{
"internal": false,
"type": "Asset & ...
- 135
0
votes
1
answer
1k
views
"0 records imported" when importing JSON file to MySQL Workbench
When I follow the Table Data Import Wizard I can see that it has successfully parsed the JSON file in the preview it provides. However, at the end, I see "0 records imported". This happens ...
- 1
2
votes
1
answer
1k
views
Modify a JSON key value based on another key's value in SQL Server
I have a table with a column containing JSON objects. Within these objects, I need to locate the key type with a value of 3. Once found, I need to update the key price with a new value of 99 in the ...
- 505
0
votes
1
answer
471
views
how to contraint that jsonb column must be a json/object?
I have a column that have json datatype
how to make a constraint or validation in my postgresql to validate that this jsonb column value must be a hash/object/json
because apparently after some ...
- 103
3
votes
2
answers
630
views
Nested JSON expressions are converted to strings after a union
The following query properly nests ContactAddress as an array under the outer JSON object:
select
Json_Query(IsNull((
select [Name], [Id],
(IsNull((
select [...
- 197
1
vote
1
answer
768
views
PostgreSQL json(b)_to_record with dynamically named keys?
I have a table with, among other fields, a jsonb field "translations" containing translations and some other properties, there can be any number of properties on it. It's shaped like this:
{
...
- 151
0
votes
0
answers
101
views
how can I add a unique restriction on a column with json data for a specific key
I have this database table
create table db_etennisnlive.bss_custom_option_template
(
template_id int unsigned auto_increment comment 'Template ID'
primary key,
title ...
- 159
0
votes
1
answer
106
views
how to access JSON data from a mysql JSON column
I have the following entry in a JSON field in MySQL, How can I access the wid_1 -> pct_usage data?
{ "wid_1": { "cof": 1, "pct_usage": 50.0, "tid": 69285334, ...
- 2,126
0
votes
2
answers
120
views
Find rows with given coordinates nested in a JSON column
I have a table public.polygon_versions in my PostgreSQL 14.7 database:
CREATE TABLE public.polygon_versions (
id bigint NOT NULL DEFAULT nextval('polygon_versions_id_seq'::regclass),
entity_id ...
- 147
1
vote
0
answers
139
views
MongoDB Compass JSON Document Failed Validation
Apologies in advance if I am asking this question in the wrong place. I have read a number of similar questions and responses on this topic but many seem outdated and don't seem to fit my issues.
I am ...
- 11
1
vote
1
answer
1k
views
How can I query using like on json field in PostgreSQL?
I've set up the following DB Fiddle: https://dbfiddle.uk/mzsp-4tg
How should I make a correct SQL query to use like on geo_json field of json type to get table records with string matching coordinates ...
- 147
3
votes
1
answer
770
views
How can I select data as JSON using a sub query with a string literal?
I am trying to write a query that will transform the data into JSON and can't seem to get it right.
I have two table, Configuration & Provider. Configuration has a FK to provider.id.
This is my ...
- 33
0
votes
1
answer
37
views
Source tables from JSON API extraction
Trying to implement an API extraction that dumps the results from JSON apis daily in a postgres db. The goal for these data is to be used in analysis. Afterwards I will handle those using dbt to do ...
- 103
1
vote
2
answers
1k
views
How should I store nested JSON object inside Cassandra?
This is the first time I am working with Cassandra and I have a data structure like following, want to save it inside the Cassandra:
{"user_id": "123",
"user_cards": {
...
- 119
3
votes
1
answer
307
views
Return recursive self joins in PostgreSQL with aggregated data
I have this simple table schema:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL, ...
- 165
0
votes
1
answer
47
views
Postgres join as inner json
Just trying to do a simple inner join as json.
model
posts (author is fk to profiles.id)
id
author
title
...
profiles
id
displayName
...
Trying to do a simple join so that:
select * ...
- 153
2
votes
2
answers
11k
views
SQL Server error JSON text is not properly formatted. Unexpected character '?' is found at position 21
I am getting below error while parsing any JSON string with special character or emoji. How to parse this?
JSON text is not properly formatted. Unexpected character '?' is found at position 21.
...
- 21
1
vote
0
answers
34
views
Allow procedure to get multiple (but not defined) number of input variables
I have a serie of statements like this one:
SELECT `Article`.`id`, `Article`.`title`,
CONCAT('[', GROUP_CONCAT(DISTINCT
CASE WHEN (`junction_article_author`.`authorRank` IS NULL AND `Authors`.`...
- 111