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.
669
questions
-1
votes
0
answers
22
views
Check if Value contains all JSON column values - reverse of JSON_CONTAINS
I have JSON column in MySQL that contains simple one level JSON
{"A", "B"}
and I'm trying to find if all the values in this columns are in provided VALUE
{"A", "B&...
- 99
4
votes
1
answer
99
views
PostgreSQL json testing (`is json`) and casting with null character (`\u0000`) work unexpectedly
I know something about null characters in PostgreSQL text types. But I cannot understand the strange behavior of json testing and casting:
select '{"foo":"bar\u0000"}' is json; --...
0
votes
1
answer
48
views
How to structure a database with multiple 1-N relationships?
I'm looking for advice on how to model the tables for the following entities. The main concept is a Job. There are two Job types, regular ones, and case studies. This question is about case studies.
...
- 123
0
votes
1
answer
34
views
Unable to retrieve data from postgres json column
I have a postgres table with json column as dataset_metadata. It stores an epoc time in array looks like:
select
event_type
,technology
,dataset_metadata->> 'intervals_epoch_seconds'
,add_ts ...
- 281
0
votes
1
answer
34
views
How to input a variable from an insert statement into the json value in PSQL?
I want this statement to insert the variable data in my psql statement but I do not know how to initialize the data within the json statement.
INSERT INTO audit_log (
action,
result,
...
0
votes
2
answers
79
views
Query nested json columns in postgresql
I want to return Germany and UK from the below json column/string.
{
"id": 1,
"name": "phone",
"components": [
{
"id": 1,
"...
0
votes
1
answer
19
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
114
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
52
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 ...
- 152
1
vote
1
answer
81
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:
...
- 385
0
votes
1
answer
59
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
101
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
70
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
32
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
292
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
58
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,672
1
vote
1
answer
121
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
204
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
343
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
91
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
423
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,412
0
votes
1
answer
304
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
207
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 | ...
- 209
2
votes
1
answer
585
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
300
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,412
0
votes
1
answer
139
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
182
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
1
answer
197
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 ...
- 217
2
votes
1
answer
311
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
2k
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
1k
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
169
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
48
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
59
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
776
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
739
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
2k
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
2k
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 ...
- 527
0
votes
1
answer
670
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
823
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 [...
- 207
1
vote
1
answer
1k
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
1
answer
116
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,166
0
votes
2
answers
157
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 ...
- 157
1
vote
0
answers
164
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
2k
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 ...
- 157
3
votes
1
answer
1k
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
38
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
2k
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
337
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
54
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