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
26 views

Importing CSV with a json array of strings in one of the column

The columns of the CSV are of the format text (can include empty lines), date and a JSON array of strings (something like ['a', 'b', 'c']. I've been trying to copy that CSV to a PostgreSQL table (...
user avatar
  • 3
0 votes
1 answer
8 views

Json extract of ['a','b','c']

My json is of the format ["a","b","c"]. I want to write a sql query such that select * from mytable where a_column in json_extract["a","b","c&...
user avatar
2 votes
3 answers
47 views

Generate JSON with property names that include dot using SELECT FOR JSON

I need to generate the folowing JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData. { "Id" : "A1"...
user avatar
  • 23
2 votes
2 answers
160 views

jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"

Using: Postgres 14.2. Objective: To get a list of all distinct countries in my table. The column address is a JSONB column type and contains an array structured like: { "address":[ {...
user avatar
  • 123
0 votes
1 answer
21 views

Join variable number of rows in Postgres into json array

I have the following table: timestamp | type | value | source ----------+------+-------+-------- 2020-01-01| 1 | 10 | 2 2020-01-01| 2 | 20 | 2 2020-01-02| 1 | 5 | 4 2020-01-02| 2 ...
user avatar
  • 103
1 vote
0 answers
37 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
user avatar
0 votes
0 answers
29 views

Oracle 19c-ee CTX_REPORT.INDEX_SIZE and CTX_REPORT.INDEX_STATS cannot be executed

We created a simple search index, based on a 19c-ee instance of the official Oracle Docker images, with the following script: CREATE TABLE EVT ( ID VARCHAR2(36) NOT NULL, EVENT BLOB NOT ...
user avatar
  • 11
0 votes
0 answers
15 views

Storing Metadata for Continuous vs Discrete data - Is Consistency or Relational Modeling best?

I'm designing a timeseries oriented database and I'm torn a bit on how to manage the dimension table for the history table. The history table stores a configuration_id, timestamp, and value, and there'...
user avatar
  • 13
0 votes
0 answers
14 views

Standard solution to join 3 related data-group

i have 3 data-group stuffs - users - notifications each stuff for each user has deferent notification state and i dont know witch model is standard for connect them, I do not want to get into trouble ...
user avatar
1 vote
1 answer
18 views

JSON_EXRACT vs native SQL query

I was originally going to ask this on SO, but thought better of it, so I am trying my hand here. I have searched this out and can't find any definitive data. I have a MySQL table that looks like: | ...
user avatar
  • 113
0 votes
1 answer
24 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 || '" : &...
user avatar
0 votes
1 answer
18 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&...
user avatar
  • 13
1 vote
2 answers
34 views

Two OPENJSON, return each array element in separate rows

I have a table where I save in JSON format the quantity that exit and return of a product. The data saved in the two columns out_quantity and in_quantity is in array format. CREATE TABLE products_info ...
user avatar
  • 13
1 vote
1 answer
54 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
user avatar
  • 203
3 votes
1 answer
303 views

Best way to map different JSON keys to same target columns

I have CSV import data that comes from multiple vendors with 50+ different columns, and each vendor has slightly different naming. Instead of trying to spend hours mapping all of the column names to a ...
user avatar
  • 203
0 votes
1 answer
197 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, ...
user avatar
0 votes
0 answers
15 views

Saving JSON files in SSMS renders them unparsable?

I have some query that outputs a JSON, i.e., select 1 AS 'test' for json path, without_array_wrapper After clicking on the result, which is {'test':1}, SSMS 18.10 opens a file with the JSON output. ...
user avatar
1 vote
1 answer
36 views

design Postgres database schema for arbitrary JSON attribute queries

In my Postgres database, I have got lots of data (a million rows) and the schema looks like this: create table test_tb ( id int, tags jsonb ); A few queries to be performed on this dataset are: 1....
user avatar
  • 13
3 votes
2 answers
45 views

Should JSON columns be separate from a wide table in MySQL?

JSON is stored similar to LONGTEXT datatypes. The MySQL docs advise for TEXT datatypes: If a table contains string columns such as name and address, but many queries do not retrieve those columns, ...
user avatar
0 votes
3 answers
97 views

How to convert json array to json object in PostgreSQL?

This is My Column cmt_json_value which has values which is of type json array. [{"name": "Pending", "value": "PENDING"}, {"name": "Error", &...
user avatar
0 votes
1 answer
31 views

is Postgres jsonb scalabe for big documents and -> and --> operators

Today we have a table with a large jsonb (20mb). We are reading/writing the entire documents, but it's nearly fine. (60000 rows) Is it scalable (100mb-200mb, 400000 rows) if we evolve to select ...
user avatar
  • 197
1 vote
1 answer
59 views

Aggregate multiple columns and rows into a json array

I try to query multiple columns and rows from a table to create the xy-pairs for a step-linechart in json format. I need the json like this: [[63115200,213.4], [139769280,213.4], [139769280,213.39], [...
user avatar
1 vote
1 answer
255 views

Extract JSON object inside of an JSON array then search base on ID

I have a problem extracting data in the JSON column. I'm new to this JSON method, unlike relation tables. Sample Table: every minute/second all websites activity will be saved in 1 column alongside ...
user avatar
0 votes
0 answers
37 views

Query is not returning array of json response

I am trying to get the array of json response in postgresql. Here is the query which I was trying: select array_to_json(array_agg(row_to_json(componentLevels))) from ( select q1_sales_tracking_q1_req ...
user avatar
0 votes
0 answers
25 views

How to create jsonb structures from function variables?

I have some ugly, but working, (plpg)SQL: CONCAT('{"mtlogonsuccess":{"ts":"', now_json, '"}}')::jsonb); (This is within VALUES() within an INSERT INTO, within a trigger ...
user avatar
  • 133
0 votes
1 answer
41 views

How to use json_arrayagg function on more than one pivot table

As the title suggests consider the following example where there are three tables and two pivot table #Tables user(user_id char(42)); role(role_id char(42)); task(task_id char(42)); #Pivot tables ...
user avatar
0 votes
0 answers
33 views

How to customize jsonb field in postgresql to accept 40GB

Background I am data scientist. I had tried minimize my instance to smaller version, but unfortunately the recalculated result from smaller instance is wrong. After I check with the original value. I ...
user avatar
  • 265
0 votes
1 answer
76 views

Using OPENJSON, can't get results to GROUP BY

I'm passing a JSON string to a stored procedure in order to insert warehouse inventory. Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (...
user avatar
  • 3
0 votes
1 answer
46 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", ...
user avatar
0 votes
2 answers
186 views

Using nested json_arrayagg with two table which have many to many relationship

Consider following two tables in mysql database role(role_id); task(task_id); and a pivot table role(role_id,task_id) I can extract a single role in JSON format using the following query select ...
user avatar
0 votes
0 answers
36 views

Why JSON indexing take less storage than multiple columns in MySQL 8?

I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between a table multiple_cols with 2 columns num INT and str CHAR(200) having each an index, a table json_indexing with 1 ...
user avatar
1 vote
1 answer
114 views

json_modify on condition

As in my previous question, I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify I'm used to using .modify with XPATH to be able to modify XMLs on the ...
user avatar
2 votes
1 answer
118 views

Return a resultset as JSON text—using pretty formatting

In Oracle 21c, it looks like we can return a resultset as JSON text: SELECT json_object(*) from dual; result: {"DUMMY":"X"} That works in db<>fiddle: Question: Is there a ...
user avatar
  • 1,373
1 vote
1 answer
1k views

How to query nested json strings in jsonb field in postgres?

In our database, one JSONB column contains an object which has a property which is a string encoded JSON object. I need to deserialize this string during the query and inspect its values. create table ...
user avatar
  • 123
1 vote
1 answer
67 views

Enumerating JSON arrays with OPENJSON

I've been working on converting some of my XML based stored procedures to JSON, but I'm running into an issue. With OPENXML, I had access to mp:id, so when I was parsing an XML, I could access an ...
user avatar
2 votes
1 answer
108 views

How to obtain the path to the match of a JSONPath query in PostgreSQL 14?

Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON: select jsonb_path_query( $$ { "id": &...
user avatar
  • 121
0 votes
1 answer
25 views

Mongo DB Query does not give desired result

I have two collections in mongo db. One called list with keys id and movieid in it. One called movies with movieid and Genre in it. The results I am trying to retrieve are: Which listid has the ...
user avatar
  • 1
0 votes
0 answers
35 views

How to find a document with the highest value of a certain property?

Here is a sample of the collection I have in MongoDB called list { "Id" : 1, "Name" : "Music", "PicId" : 1 }, { &...
user avatar
  • 1
0 votes
1 answer
82 views

Efficient where clause on field that has json

I have the following query that i would like to add a where clause on a json field, Below is my json I filter out based on a list of categories, in below example, there is 3 categories and if i pass ...
user avatar
  • 325
1 vote
2 answers
350 views

SQL Server how to upload big json into column, performance issue

I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data. Here my table structure : CREATE TABLE [dbo].[tbl_set_Cart]( ...
user avatar
  • 287
1 vote
1 answer
88 views

Convert PostgreSQL table to structured JSON output

I have been having a horrible time dumping this example output into a neatly structured JSON output. Here is some example data CREATE TEMP TABLE isuck (path text[], city text); INSERT INTO isuck(path, ...
user avatar
  • 11
0 votes
1 answer
125 views

SQL Server select into vs. insert into performance with embedded cross apply openjson

Note: updated 11/15/2021 to address comment feedback. It seems SQL Server 2016 has some odd little corners when it comes to performance. Version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13....
user avatar
  • 21
0 votes
1 answer
20 views

Producing JSON in Oracle 12c1

I need to produce a JSON document with several properties from a list of Oracle 12c1 production databases, and I was given read-only access in order to generate this data. The problem is these ...
user avatar
1 vote
0 answers
42 views

Storing user defined forms and their data

I'm writing an application that would have a feature similar to Google Forms. In this feature, some end users of the system will have the ability to design custom forms. Those forms will subsequently ...
user avatar
  • 111
0 votes
0 answers
39 views

Database based on nesting JSON

I currently am storing raw JSON in a table as I cannot figure out how to create a table structure for the data I am using. The JSON example I have is: { "type": "HEADER", "...
user avatar
0 votes
0 answers
72 views

JSON Search index usable with 'LIKE' but not with '='

We are seeing an issue with Oracle 19c and a JSON search index. Given: A table GEO containing a column JSON_DATA with JSON data and an IS_JSON constraint An index created like this: create search ...
user avatar
0 votes
0 answers
161 views

PostgreSQL query on jsonb column is not using indexes

I have the following table and indexes where all those indexes were created to try and find a solution to my issue: create table persistent_events ( "notificationId" ...
user avatar
0 votes
0 answers
94 views

Fastest way to insert in-memory JSON data into Postgres, other than json_populate_record

We ingest a lot of data, and one of the most time-consuming tasks is inserting data that NodeJS has in JSON format into the database. Currently we're running a query like this, with the giant JSON ...
user avatar
  • 101
0 votes
0 answers
123 views

How to improve efficiency of multiple JSON_EXTRACT SELECT expressions

In MariaDB + InnoDB, I have a table like: CREATE TABLE `stuff` ( ...
user avatar
-1 votes
1 answer
73 views

Why isn't GIN index being used for JSONB exact match on text?

I have this index: CREATE INDEX ginner ON public.documents USING gin (data) And "explaining" this query: select * from public.documents d where data::json->>'name' = 'Barbara Fritsch' ...
user avatar

1
2 3 4 5
12