Skip to main content

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
19 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, ...
livelypeach's user avatar
0 votes
2 answers
25 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, "...
Jackie Ndinwa's user avatar
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",&...
Adam Hewitt's user avatar
0 votes
1 answer
80 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 ...
David McNeill's user avatar
0 votes
1 answer
31 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 ...
Robert N's user avatar
  • 133
1 vote
1 answer
56 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: ...
Gea-Suan Lin's user avatar
0 votes
1 answer
49 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 [...
dexon's user avatar
  • 47
0 votes
1 answer
60 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 ...
Vidya L's user avatar
  • 101
0 votes
1 answer
58 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 ...
Henry's user avatar
  • 213
0 votes
1 answer
22 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,...
Amir Pashazadeh's user avatar
1 vote
1 answer
181 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 ...
user1664043's user avatar
0 votes
1 answer
49 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 ...
tinlyx's user avatar
  • 3,610
1 vote
1 answer
80 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 = ...
Andreas Hinderberger's user avatar
-1 votes
3 answers
172 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 ...
Ehsan Chavoshi's user avatar
0 votes
1 answer
156 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 ...
Michael's user avatar
  • 153
0 votes
0 answers
75 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 ...
Damo's user avatar
  • 193
4 votes
1 answer
334 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. ...
Peter's user avatar
  • 2,362
0 votes
1 answer
221 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 ...
Niki's user avatar
  • 1
0 votes
1 answer
145 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 | ...
Don Draper's user avatar
2 votes
1 answer
410 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 ...
WannabeCoder's user avatar
4 votes
2 answers
285 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, ...
Peter's user avatar
  • 2,362
0 votes
1 answer
87 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 ...
jberryman's user avatar
  • 471
0 votes
1 answer
141 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 ...
Gmanicus's user avatar
0 votes
0 answers
104 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 ...
psygo's user avatar
  • 121
0 votes
1 answer
169 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 ...
Ian Newson's user avatar
0 votes
0 answers
212 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 ...
harald's user avatar
  • 121
2 votes
1 answer
277 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: {"...
Karthika's user avatar
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":...
GAURAV RATHOD's user avatar
1 vote
1 answer
782 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 ...
psygo's user avatar
  • 121
2 votes
1 answer
160 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&...
user275716's user avatar
1 vote
1 answer
39 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&...
Khuzema's user avatar
  • 13
0 votes
1 answer
53 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 ...
Khuzema's user avatar
  • 13
2 votes
1 answer
660 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 & ...
Prosy Arceno's user avatar
1 vote
1 answer
604 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 & ...
Prosy Arceno's user avatar
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 ...
zach's user avatar
  • 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 ...
lifeisajourney's user avatar
0 votes
1 answer
574 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 ...
buncis's user avatar
  • 103
3 votes
2 answers
729 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 [...
N8allan's user avatar
  • 197
1 vote
1 answer
958 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: { ...
svenema's user avatar
  • 151
0 votes
1 answer
110 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, ...
IGGt's user avatar
  • 2,146
0 votes
2 answers
135 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 ...
Taras's user avatar
  • 157
1 vote
0 answers
150 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 ...
CG3's user avatar
  • 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 ...
Taras's user avatar
  • 157
3 votes
1 answer
899 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 ...
awesomegar's user avatar
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 ...
Igneel64's user avatar
  • 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": { ...
best_of_man's user avatar
3 votes
1 answer
322 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, ...
Sirjon's user avatar
  • 165
0 votes
1 answer
49 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 * ...
Jonathan's user avatar
  • 153
3 votes
2 answers
13k 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. ...
Mish's user avatar
  • 33
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`.`...
fauve's user avatar
  • 111

1
2 3 4 5
14