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.
657
questions
2
votes
1
answer
88
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
257
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,082
0
votes
1
answer
25
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
...
- 461
0
votes
1
answer
20
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
41
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
32
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
40
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
64
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
182
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":...
- 139
1
vote
1
answer
74
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
112
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
20
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
39
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
253
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
85
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
450
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
627
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 ...
- 435
0
votes
1
answer
180
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
487
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
0
votes
1
answer
235
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:
{
...
- 141
0
votes
0
answers
30
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 ...
- 149
0
votes
1
answer
66
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,066
0
votes
2
answers
115
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
84
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
297
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
0
votes
0
answers
227
views
Mongoexport query using date range on Linux
I'm trying to run mongoexport using this query:
mongoexport --db=database --username=user --password=pass --collection=coll --type=csv --fields=Name --query='{ "Date" : { "$gte" : {...
Fernanda Andretto
3
votes
1
answer
256
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
34
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
683
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
193
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, ...
- 65
0
votes
1
answer
34
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 * ...
- 143
2
votes
2
answers
7k
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
0
votes
1
answer
799
views
Postgresql : merge rows of json key-value into one flat dict (not an array)
In Postgresql, I want to build a flat JSON "dict" out of returned rows of key/values
When I call :
select json_agg( jsonb_build_object(key, COALESCE(value,'')) ) from props;
I get spurious ...
- 101
1
vote
1
answer
174
views
Returning array of JSON objects from related fields in PostgreSQL
I have the following schema and some data inserted into the DB.
Schema (PostgreSQL v14)
CREATE TABLE users(
id INT NOT NULL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
...
- 65
4
votes
2
answers
441
views
Pair top-level key with nested value from jsonb document
Given the following data:
create table datas (id int, data jsonb);
insert into datas (id, data)
values
(1, '{"key1": {"foo": "bar1","some":"thing1&...
- 43
1
vote
0
answers
791
views
Choosing between Entity-Attribute-Value or JSONB representations
I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table ...
1
vote
1
answer
1k
views
How to filter rows based on a nested JSON array field?
[
{
"id": 1,
"columns": [
{
"id": 16,
"expression": "foo"
},
{
"id": 12,
"...
- 197
0
votes
0
answers
592
views
Update json string column with proper json in Postgres SQL
I have a column which type is JSON but it contains JSON strings like this:
"{\"a\":1,\"b\":2,\"c\":3}"
I want to update the values in the column with proper ...
- 101
0
votes
1
answer
497
views
How to read integer array values coming in JSON parameter in Postgres - Missing "]" after array dimensions
I'm very new to Postgres and trying to insert the array integer values into a table as multiple rows.
Here is the json string
'{
"types": [3,6]
}'
trying to read and insert the ...
0
votes
0
answers
403
views
Replacing Multiple Rows Value In a JSON Column
I am trying to create a script in SQL Server which its only job is replacing JSON value in the JSON column. The values of that column is a dynamic JSON. It could be a single nested JSON or 6 depth at ...
0
votes
0
answers
22
views
In DolphinDB, how to parse json string in SQL statement?
Suppose we have the following table:
id val1
1 {"a":1,"b":2}
2 {"a":3,"b":4}
3 {"a":0,"b":2}
I am ...
0
votes
3
answers
138
views
On duplicate, conditional update, not working
I have a table with two columns : id(int), data(text)
data holds an array of strings. I want to insert strings into it, if they're not already present.
Searched a bit, came up with this :
INSERT INTO ...
- 165
0
votes
0
answers
152
views
How to remove a certain field from JSON column which stores array of object s
We are using MySQL 8.0. We have a JSON column(options) that stores an array of objects. I want to use this field in my Select query but I don't need the point field. I tried JSON_EXTRACT, and ...
- 239
5
votes
1
answer
2k
views
MySql Json Replace An array Value
MySql Json Replace An array Value at Specific index if the value matches some condition
Example:
{
"deploy": [
"Infrastructure",
"API Security"
],
"...
- 53
3
votes
0
answers
281
views
Full-text search index across columns and tables
I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
- 81
0
votes
0
answers
570
views
Load JSON data into postgres table using airflow
I have an Airflow DAG that runs a spark file (reads two parquet files, performs transformations on them, and loads the data into a single JSON file).
Now the data from this JSON file needs to be ...
-1
votes
1
answer
2k
views
How do I parse this JSON array into columns with the respective answer?
I have a column with JSON in postgresql that has over 5,000 records like this (this is one row/record).
How do I get this parsed into each field id becoming a column (1, 2, 3, etc.) with the ...
- 3
0
votes
1
answer
1k
views
count occurances inside a json array
I have a table with a column that holds a JSON array, and I want to count the array members that match a certain criteria. When I run the query I always get a count of zero, so I assume that I have ...
- 103
2
votes
3
answers
938
views
Why Optimize Table does not shrink table size?
I implemented this solution for big database, only keep data for 14 days (remove data daily based on date). When I run optimize TABLE table1; The size supposes to decrease but in my case, it increases....
- 218