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
0 answers
2 views

Updating parameter values from a postgresql SP InOut JSON parameter

The JSON is being passed into a PostgreSQL SP _get_xml_status(INOUT i_json json) I need to update the xmlInfo.Status parameter for each of the xmlName nodes depending on what I find for each entry in ...
0 votes
0 answers
7 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","...
  • 1
1 vote
1 answer
31 views

Converting rows to desired JSON format in TSQL

Trying to convert the data in required JSON but wasn't successful, any help or suggestions would be appreciated. SQL Server version: 2019 Test script: drop table if exists t1 go create table t1(id int,...
0 votes
1 answer
25 views

How to select all nested values at a particular level in a json structure in a JSONB type column in postgresql?

In a postgresql database, I have a table with 2 columns: Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312 Column2 is of type JSONB and contains multiple rows of ...
0 votes
1 answer
22 views

How do I query inside a JSONB column when all values have the same json-like structure?

In a postgresql database, I have a table with 2 columns: Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312 Column2 is of type JSONB and contains multiple rows of ...
0 votes
1 answer
20 views

How to parameterize a Postgres JSON containment query with arrays of objects?

I have a JSON containment query as below. The query looks for matching objects inside arrays. This works. The values of Name and DataType will be passed in to a .Net function, and the query built in ...
0 votes
0 answers
37 views

Is PostgreSQL array_to_json is a bad practice?

I have a habit of writing queries returns as JSON structures directly from the PostgreSQL query, -- Something like this... -- The function CREATE OR REPLACE FUNCTION get_data(_team_id UUID) RETURNS ...
  • 101
-1 votes
1 answer
38 views

NoSQL DB candidate for the project (looking for experienced advice)

Intro I am stuck with the limitations of the DBs I've tested* and need someone experienced to give me some advice. Project details Data sets The project has 2 sets of data, the sets of items and their ...
1 vote
1 answer
56 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
  • 11
0 votes
0 answers
15 views

Postgresql - text Array to Json Array error

I am getting an error while trying to convert string array to json array in postgresql. SQL: select * from jsonb_array_elements_text(to_jsonb('[{\"Apr2021\":\"1.2\",\"...
  • 133
0 votes
1 answer
25 views

Embed column containing JSON in FOR JSON AUTO

If I have a table with a column containing an arbitrary valid JSON document, can I embed that document into a query returing JSON other than as a string? For example: CREATE TABLE #Example (Name ...
  • 2,618
0 votes
1 answer
19 views

what should I do q jsonb query in postgreSQL 13

I have stored jsonb data in PostgreSQL 13 like this: [{"code": "OFFICIAL"}, {"code": "FULLTEXT"}] how to query the jsonb data type? I have tried like this: ...
  • 527
0 votes
1 answer
61 views

Changing a single element of an json array in PostgreSQL

First of all I'd like to say that I don't have much experience but I started learning dbs after having a couple of subjects related to dbs at my university (so please keep that in mind while you're ...
  • 3
1 vote
2 answers
35 views

MySQL: Virtual Column based on JSON boolean value - convert boolean to tinyint possible?

I have a table with a json field attributes and a virtual tinyint (boolean) column flights with the following configuration: JSON_UNQUOTE(attributes->"$.flights") The flights property in ...
  • 111
1 vote
1 answer
105 views

MySql 8 nested json aggregation functions

I'm struggling trying to aggregate results in a table into a nested json. this is the table: +----+------+--------+------+------+-------+-----------+--------------+ | id | area | userId | game | step |...
0 votes
0 answers
28 views

Should I avoid JSONB column for subschemas?

I'm working on a schema for an audit subsystem and I'm wondering about the tradeoffs of some potential design choices. About the schema, each event holds the same critical metadata event_id, ...
1 vote
1 answer
104 views

Mysql update json if-else exist

I want to update my raw with if-else condition. Here is my sql code; update posts set like = if(json_contains(like,2,'$'), json_remove(like,replace(json_search(like,'one',2),'&...
0 votes
1 answer
32 views

parse JSON keys preserving ordinality

I had heard that in postgresql, the json type preserves declared ordering unlike it's fully-parsed counterpart jsonb. I want to get the ordinal position of each key in a document as supplied by the ...
3 votes
1 answer
192 views

Update all instances of key inside nested JSON array

I'm trying to update a complex JSON document stored in a table in CockroachDB. The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer ...
  • 31
5 votes
2 answers
431 views

Records greater than epoch timestamp using only LIKE operator

I have the following query so far and unfortunately, I cannot use regexp or greater than operators, I can only use the LIKE keyword. The whole column is in a json string, I can't use json_value or ...
0 votes
1 answer
313 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'{ &...
  • 173
2 votes
1 answer
624 views

How to sum nested values from a JSON array based on conditions

I have a table with 3 columns in Postgres 14.2: name, name_adds, aditional. And these rules: name and name_adds can be filled with the same value or not. Example: name (john) and name_adds (doe) or ...
  • 41
0 votes
2 answers
756 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: [ { &...
4 votes
1 answer
1k views

Storing special characters (German, French, Spanish) in SQL Server result in weird strings

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße" But this also happens for other characters: "König-...
  • 233
0 votes
1 answer
129 views

Specify range of Json array from different column

Mysql 8 makes it possible to extract part of an array saved in a JSON column as shown below: SELECT jcol->>'$[1 to 5]' FROM `db`.`table` which 1 and 5 are boundaries of the desired range of ...
1 vote
1 answer
87 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/...
  • 133
0 votes
1 answer
509 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 (...
  • 3
0 votes
1 answer
60 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&...
2 votes
3 answers
348 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"...
  • 23
4 votes
2 answers
3k 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":[ {...
  • 143
0 votes
1 answer
119 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 ...
  • 103
1 vote
0 answers
440 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
0 votes
0 answers
70 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 ...
  • 11
0 votes
0 answers
24 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'...
  • 13
0 votes
0 answers
16 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 ...
1 vote
1 answer
99 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: | ...
  • 113
0 votes
1 answer
157 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 || '" : &...
0 votes
1 answer
64 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&...
  • 23
1 vote
2 answers
635 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 ...
  • 13
1 vote
1 answer
281 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": "...
  • 203
3 votes
1 answer
556 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 ...
  • 203
0 votes
1 answer
2k 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, ...
0 votes
0 answers
27 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. ...
1 vote
1 answer
97 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....
  • 13
3 votes
2 answers
234 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, ...
0 votes
3 answers
2k 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", &...
0 votes
1 answer
93 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 ...
  • 199
1 vote
1 answer
1k 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], [...
2 votes
1 answer
3k 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 ...
0 votes
0 answers
52 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 ...

1
2 3 4 5
13