Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
23 views

Simplifying database structure with many unequal vectors

I want to find an efficient way to represent the JSON objects below. I made an ERD diagram to represents the JSON objects. This diagram below is a small piece within a much larger structure. There ...
1
vote
1answer
17 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
-1
votes
0answers
28 views

Create a table in query using json object

I'm trying to store each object from a json string in a table. I'm using this function I found to get all elements from a json and split into a table and show it in a select. With this parseJson ...
0
votes
1answer
11 views

Postgres JSON output using specific column as JSON key

I have data in Postgres (10.4) in the form of id | my_json ----------------------------------- 1 | {"a": 1, "b": 2, "c": [1,2,3]} 2 | {"a": 3, "b": 5, "c": [2,3,4]} I can output the data as JSON ...
0
votes
1answer
18 views

Postgresql JSONB Column, how to query for multiple specific elements in a JSON array

Giving the following JSONB data structure of a stopInfo column in a myTable table: { "stopInfo": [ { "stopType": "Origin", "partnerId": "KR01", "locationId"...
0
votes
0answers
39 views

Convert Text To JSON

I want to convert columns format from text to JSON but I get an error like this **ERROR 1031: Table storage engine for '#sql-2184_71821b' doesn't have this option SQL Statement: ALTER TABLE `...
2
votes
1answer
28 views

Handling variable fields in a CSV file with JSON?

Im trying to figure out the engineering to get a CSV file into PostgreSQL. Requirements: The CSV has X number of predetermined required fields The CSV file has Y number of dynamic fields Example: ...
0
votes
1answer
38 views

Trigram index on all values of a JSONB column

I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6. ...
1
vote
1answer
61 views

Aggregate a JSON doing an average on values without defining keys

I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's ...
0
votes
0answers
27 views

How can I increase the UPDATE performance for jsonb fields? PSQL 9.6

I have a relatively large table, with less than 10 fields, containing close to a million rows currently but it will accumulate more than 10-15 millions in the near future. One of the fields is: jsonb,...
2
votes
1answer
41 views

How to sum (aggregate) all the values in a key/value JSONB?

in Postgres 10 I have the following table: CREATE TABLE testtable ( id int PRIMARY KEY, qty jsonb ); INSERT INTO testtable (id,qty) VALUES ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "...
0
votes
1answer
18 views

How to work with JSONB column

I use Postgres and jsonB column I have the following table: id(VAARCHAR(64)) | value(JSONB) -------------------------------- 1 |[{"x": "10", "y": 1}, {"x": "32110", "y": 12}] X has ...
0
votes
0answers
36 views

JSON_SEARCH, mysql 5.7 vs mariaDB

I am working with the following two machines and databases: Machine 1 Fedora 26 mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper** Machine 2 Debian 9 mysql Ver 15.1 ...
1
vote
1answer
60 views

What is the fastest way to verify that a json[] array is not empty?

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on: the_array != '{}' However, that does not work for a json[], as demonstrated below: select '{}'::...
0
votes
1answer
39 views

How make recursive json on mysql

my table looks like CREATE TABLE `test_comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `test_id` int(10) unsigned NOT NULL, `user_id` int(...
3
votes
2answers
373 views

Cursor causes SSMS to crash

I have a cursor that generates one record of JSON text from a group of tables. The cursor has been making SSMS crash. The script runs for a time then SSMS fails. Below is the code that I have ...
1
vote
1answer
23 views

Transform/map json object array to primitive for GIN indexing

Say I have a table with a jsonb column that contains data structured like this: { "records": [ { "id": 1, "tag": "address" },{ "id": 1, "tag": "receipt" } ] } ...
0
votes
0answers
23 views

JSONB: function which sorts only JSON arrays

I've just finished SQL code which checks if particular "query" fulfils "conditions" stored in rows. If yes, id-s of that rows are returned. This is made up example. CREATE TABLE jsonb_test ( id ...
0
votes
0answers
92 views

Update jsonb array without index in postgres 9.6

I have a json array in a database field with name filters in the following format '{"org":[219,160,125]}' I need to update the array to '{"org":[219,160]}' using query for all rows in the database. ...
0
votes
1answer
72 views

How to query inside jsonb column

I have a column type of jsonb named changes THis is the structure of my column. [["change","is_enabled",[false,true]]] I want to query within the column. I want to find all the rows that "...
0
votes
1answer
61 views

sql server for json

I want something like this { "customer":[ { "custid":1, "companyname":"Customer NRZBB", "order":[ { "orderid":10643, ...
0
votes
2answers
157 views

Best SQL datatype for JSON

In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar. Most of our JSON strings will not use more than 100 characters. Should I use nvarchar(100) or nvarchar(max)? We prefer ...
2
votes
2answers
405 views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['George', 'John'] 2 | ['Thomas', 'James'] 3 | ['James', 'John'] How to list the number of bands each ...
1
vote
1answer
235 views

MariaDB compare JSON

How to compare two JSON fields in MariaDB? MariaDB 10.2 and 10.3 - FAILS SELECT CAST('{"q":2}' AS JSON); Returns : SQL Error (1064): You have an error in your SQL syntax; check the manual that ...
1
vote
2answers
299 views

How to dump to csv valid json with Postgresql?

I am trying to dump some data as json with: \copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv ...
1
vote
1answer
421 views

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
3
votes
1answer
39 views

How to retrieve the value of deleted key in jsonb?

Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key? Thus, if the key was not found, a null is returned or ...
1
vote
1answer
793 views

postgresql: how to define a JSONB column with default value

I am unable to find in the documentation how to create a JSONB column in PostgreSQL that has a DEFAULT value of an empty json document. How the above can be stated in the CREATE TABLE definition ?
1
vote
1answer
18 views

Postgresql 9.5 JSON problems

I have to update a complex query that takes JSON fields: SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType from SELECT context->>'Source' as Product, ...
0
votes
1answer
632 views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
1
vote
1answer
339 views

Multiple rows into json

Looks my question is unusual because I didn't find the answer at all. Let's imagine, I have table A with columns: language and uri. language | uri ---------|----------------- ru | ...
1
vote
1answer
29 views

Query JSON in Azure SQL DB to get one array element per row

I have a stored procedure in a SQL Azure DB instance, and the input parameter is a JSON string formatted like this: { "OrderNumber":["OD12034"], "Site":["New York"], "LineNumber":[1000,...
3
votes
1answer
37 views

How to do this binary appearance analysis

I have a table named results: integer: id json: websites An example of websites column is: [{"url": "http://google.com"}, {"url": "http://example.com"}] What I want to do is generate a binary ...
0
votes
0answers
33 views

Parse file with non-standard layout into table

I have a file that I am getting from a vendor that I need to put into an SQL Server table. The data in the file looks like this: {"result":"success","ReqCtx_requestURI":"somethinghere","eventDate":"...
0
votes
1answer
119 views

Using JSONB Column or Another Table to Save Relationsips

I tried to search here thoroughly but did not find any answers. I have a PostgreSQL database, which has two main tables: documents users These two tables have different relations. A user can: ...
0
votes
0answers
113 views

MySQL version of split_to_array or split_to_table?

PostgreSQL supports two String Functions, that are incredibly useful regexp_split_to_array regexp_split_to_table Both of these functions essentially take two strings, (a) a pattern and (b) an input ...
1
vote
1answer
105 views

Getting specific key values from JSONB into columns

In this query attributes a JSONB column: select COUNT(order_items.attributes) as sayi, order_items.attributes from order_items INNER JOIN products ON order_items.product_id = products....
0
votes
1answer
1k views

Is the MySQL JSON data type bad for performance for data retrieval?

Let's say I have a MySQL JSON data type called custom_properties for a media table: An example of the json data stored in the custom_properties column could be: { "company_id": 1, "uploaded_by": "...
1
vote
1answer
732 views

Search for nested values in jsonb array with greater operator

Here is the table definition (simplified): CREATE TABLE documents ( document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, data_block jsonb NULL ); Sample values: INSERT INTO documents ...
1
vote
2answers
70 views

Indexed range comparison of array key's value in row inside JSONB

This is for tests with results. The order and if the test was done can be vary per row. column jdata {"name": "Somedata", "array": [ {"name":"test0","value":4.6}, {"name":"test2", "...
1
vote
1answer
38 views

Store and search leveled categories with mysql json

I have leveled category triplets column, that tag the specific blacksmith with items he can produce: blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int) ...
1
vote
0answers
118 views

Export json data into single cell

My problem is a relatively simple one; unfortunately, I haven't been able to find any solutions from searching around. I have a SQL table called alerts from which I'm trying to export data into CSV ...
1
vote
1answer
215 views

How to detect JSON data type support in MariaDB/MySQL

My first thought was to check server version but it is not the nicest solution. Is there something like PHP's function_exists() in mysql? I'd like to detect JSON_EXTRACT() or the JSON data type: ...
1
vote
1answer
64 views

Index by subkey inside subkey inside array in jsonb

I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example: d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ] f492b26d-...
1
vote
1answer
295 views

Generate nested array with OPENJSON very slow

I've just started using OPENJSON with SQL Server 2016 SP1. I have this sentence: select c.Serial as Parent, (Select co.Serial, agc.Position from AggregationChildren agc, Aggregation ag, ...
2
votes
3answers
833 views

Passing variable as parameter to json_query function

I am getting error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal ...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it. ...
0
votes
1answer
81 views

Performance of JSONB vs. integer field storage in PostgreSQL

I need to store some data with values like 0, 1 (true / false). I am considering grouping the different integer fields into one JSONB field with a structure like this: { "status": 1, "approved": ...
1
vote
3answers
2k views

Alternative to “FOR JSON” in SQL Server 2012?

In SQL Server 2016 I can output query results as JSON as described in the documentation Format Query Results as JSON with FOR JSON (SQL Server) SELECT * FROM x FOR JSON PATH But I'm finding that ...
1
vote
0answers
63 views

How do read queries against Postgres's JSONB type compare to Couchbase?

I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large ...
0
votes
1answer
167 views

Equivalent to json_strip_nulls for Postgres 9.4

The json_strip_nulls function was introduced in 9.5. What could give an equivalent result for 9.4? I have a json column that is essentially just a text column, but since the contents are JSON, we ...