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
2 answers
1k views

MySQL performance of querying json data

Compare the following 2 queries: SELECT id,props->>"$.parent" AS parent FROM device WHERE props->>"$.parent"=0 and SELECT id,props->>"$.parent" AS ...
2 votes
2 answers
18k views

Clever way to order a json item by key in postgresql (two examples)

I need to sort a json item in a record, so here are two examples: First case create table jsontable ( jsonitem json ); Next I insert a json item: insert into jsontable(jsonitem) values ('{ "...
0 votes
2 answers
776 views

Search in JSON by id and name

I have this table: id number name text row for example: id name 1 {"vals":[{"id":123,"name":""}],"additional":"text"} 2 {"vals":[{...
0 votes
1 answer
1k views

How to convert the JSON date with to_date function

How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date? That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
0 votes
1 answer
18 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
23 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, "...
1 vote
1 answer
522 views

Postgres convert to columns from string json

I am running postgresql 14.4. I have a table with 2 fields: id data_product(varchar) --- ------------ 1 [{"product_id":1,"product_code":"A1","...
0 votes
1 answer
591 views

Performance issues on json field update

I have a postgreSQL database on which I am experiencing huge latency issues when trying to update a json field using the following query: UPDATE collections SET "layout" = jsonb_set(layout, '{actions}...
0 votes
1 answer
621 views

postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like ...
1 vote
1 answer
604 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
0 votes
1 answer
3k views

INSERT array elements from JSON object as table rows

I need to insert below JSON value into a table which will have two rows as id 1708,1 and 1708,2: { "id":1708, "types":[1,2] } Below is what I have tried: insert into table(1708, ...
2 votes
2 answers
2k views

Unexpected newlines in json output in Postgresql

I'm squashing a datastructure to JSON objects using the PostgreSQL Json functions, however, there are unexpected newlines in the output. Basically, it creates JSON objects and put them in a list. This ...
1 vote
1 answer
2k views

Full text search with json field in SQLite

Is it possible to have full text search in SQLite along using json? In SQLite json is stored as TEXT (JSON1). Full text search requires creation of virtual table (full text) but I don't know how to ...
1 vote
1 answer
636 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
0 votes
1 answer
294 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 ...
1 vote
2 answers
2k 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 ...
0 votes
2 answers
4k views

Find all occurrences of a specific value in an array in a jsonb column

So, my goal is the get the count of all times when a value appears in a column. I have a table (my_table) with a jsob type column result. The value in the column looks something like this: [ { &...
1 vote
3 answers
722 views

Cannot import JSON file into MySQL

I am trying to import a JSON file into a MySQL database. It is FAA data of all the airports in the US. https://services6.arcgis.com/ssFJjBXIUyZDrSYZ/arcgis/rest/services/US_Airport/FeatureServer/0/...
0 votes
1 answer
579 views

Query Quill-Delta JSON array in Postgres

I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g. ----------------------------------------------------------------- | id | title | content ...
0 votes
1 answer
546 views

Update data type from integer to decimal128 for all document having nest array in mongo db

Had a JSON structure which is having nested array and need to update all data type for that json structure [ { student: [ { name: { firstName: "Rahul", ...
1 vote
1 answer
2k 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
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
75 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 ...
0 votes
1 answer
4k views

How to import json table into PostgreSQL table?

I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data: [{ "token": "a978f9793a594d11860e9cf7962996d7", "name": "vehicle.moving", "description": "...
1 vote
1 answer
1k views

Set difference for jsonb

The hstore type in Postgres provides an operation to calculate the set difference (EXCEPT) of two records. Is there something similar for jsonb? The best I've got is SELECT * FROM jsonb_each('{"a&...
0 votes
2 answers
2k views

How to quote arrays in postgres copy statement to insert into JSON field

I have some data that looks like this: id,bo,c,url 1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]" 2, "more text", 83, "[{'id': 'W3nAl', '...
0 votes
1 answer
4k views

Append JSON array to an object

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. id name language attr1 attr2 ...
0 votes
1 answer
1k views

Use ENUM in array of objects

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique? Data examples: ...
0 votes
1 answer
25 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 ...
1 vote
1 answer
1k views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
0 votes
1 answer
962 views

Flatten/ Concatenate Postgres JSONB array

I am trying to form a jsonb object (not array) from the table (t1) below id key value 1 hello world 1 dns 192.2.8.0 1 people 1000 I have tried SELECT jsonb_agg(('{"' || key || '" : &...
1 vote
1 answer
54 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: ...
0 votes
1 answer
48 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 [...
59 votes
7 answers
174k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
1 vote
1 answer
166 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 ...
0 votes
1 answer
776 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
1 answer
56 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 ...
8 votes
2 answers
7k 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 to ...
0 votes
1 answer
3k views

JSON - How do I get JSON Child Array into one column - OPENJSON in Synapse

I've broken this down with executable code. I need to get the lang and value columns into a single record. Any ideas on how to go about this: DECLARE @JSONMain NVARCHAR(1000) SET @JSONMain = N'{ &...
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 ...
0 votes
1 answer
21 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,...
8 votes
2 answers
12k views

Update all values for given key nested in JSON array of objects

I have a jsonb column data in a Postgres table my_table. It contains the following data: [ {"id":"1","status":"test status1","updatedAt":"1571145003"}, {"id":"2","status":"test status2","...
1 vote
2 answers
32k views

Using Python to insert JSON into PostgreSQL [closed]

I have the following table: create table json_table ( p_id int primary key, first_name varchar(20), last_name varchar(20), p_attribute json, quote_content text ) Now I basically ...
1 vote
1 answer
75 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 = ...
0 votes
1 answer
45 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 ...
5 votes
2 answers
8k views

Create JSON object from recursive tree structure

Having this simple many-to-many self-referential structure. An item owns other items through the joins table: CREATE TABLE items ( item_id serial PRIMARY KEY , title text ); CREATE TABLE ...
21 votes
3 answers
29k views

How can I use a full-text search on a jsonb column with Postgres?

So i have a jsonb column that has entries like this: https://pastebin.com/LxJ8rKk4 Is there any way to implement a full-text search on the entire jsonb column?
-1 votes
3 answers
167 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 ...
2 votes
2 answers
7k views

PostgreSQL bug with JSON type?

This query not working, but the error not make sense SELECT count(*) FROM t WHERE json_typeof(info->'vatids')='array' AND json_array_length(info->'vatids')>1; ERROR: cannot get array ...
0 votes
1 answer
2k 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 ...

1
2 3 4 5
14