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
2
votes
1answer
22 views

Convert a row to a nested JSON document containing all relations

In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the ...
0
votes
0answers
27 views

Advantages and disadvantages of JSONB columns

I love using json columns in PostgreSQL, but I'm wondering if there are scenarios where jsonb would make more sense. For example, we store a "seen by" state in our messages table and need to use ...
34
votes
6answers
47k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
-4
votes
1answer
57 views

Earliest version of SQL Server with OPENJSON

What is the earliest Microsoft SQL-Server version having the T-SQL OPENJSON function? As far as I could see before asking this question, the Microsoft manual shows the function's documentation for ...
6
votes
1answer
1k views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
0
votes
1answer
23 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
1answer
470 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: ...
1
vote
2answers
182 views

Sum(JSON_EXTRACT) MariaDB 10.3.11

I'm looking so long, and cannot find anything right concerning - ho to sum the values from JSON column. My values in the column looks like this: [{"file":"FILE.doc", "POSITION":"3245", "NUMBER":"...
1
vote
1answer
545 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
8k views

List JSON array in MySQL as rows

I have a column AddressIdentifiers of JSON type in my MySQL table Customers and the data sample looks like: [ { "code": "123", "identifier": "0219d5780f6b", "type": "BILLING", "info"...
0
votes
1answer
39 views

Passing Variable in json_value in sql server 2016 [duplicate]

declare @id int set @id = 1754 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+...
0
votes
1answer
117 views

How to unnest and display elements of a JSON array?

i have this table : And i want display all the names in the table like this : Also i want to list the number of gender each genre : Desired output:
4
votes
5answers
3k views

PostgreSQL 9.4 deep merge jsonb values

Requirements and situation We're currently using a JSONB column to speed up somewhat arbitrary searches in our database and this is working fine so far. When updating the data the requirement is as ...
0
votes
1answer
22 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
0
votes
1answer
20k views

how to query nested json objects in mongodb?

{ "_id" : ObjectId("58c7da2adaa8d031ea699fff"), "title" : "sdsddfasssdsdfsdffdf", "viewData" : [ { "_id" : ObjectId("58c7f9d03ef6c93ce964d19a"), "...
0
votes
2answers
59 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
1answer
29 views

Postgresql, export to json

Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1). Question: Is possible export to json file? Something like csv export. COPY ( SQL ) TO stdout DELIMITER '"' csv; Thanks. Update: Or can I add ...
0
votes
0answers
49 views

Is there any way to query postgres JSON/JSONB columns with ilike and indexing?

I have a table called user_data where I have JSON column called data which can have any key that can be given by application users. Here I wanted to do ilike query with using proper index. Since keys ...
4
votes
2answers
9k views

MySQL 5.7 JSON_EXTRACT: how to remove string quotes?

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes? Or should this question ...
0
votes
1answer
17 views

Postgresql and jsonb: perform a like over all values of an array

Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email: ["email1@foo.com", "email2@foo.com", "email3@foo.com"] I want to be ...
0
votes
0answers
19 views

Converting User-Defined Datatypes to JSON in Oracle

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application: CREATE OR REPLACE TYPE udf_typ AS OBJECT ( name VARCHAR2(4000), value VARCHAR2(...
2
votes
1answer
22 views

Gather values from different related rows with JSONB fields

Say I have the following table structure: jobs +----+------------+ | id | some_field | +----+------------+ | 1 | some_val | | 2 | some_val | | 3 | some_val | +----+------------+ events +----...
0
votes
1answer
26 views

How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

My Query is declare @id INT set @id = 69 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$....
1
vote
4answers
11k views

MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

we have table with JSON column for example simple: {"a": 1} {"b": 2} run JSON_MERGE query select JSON_MERGE('{"a": 1}','{"b": 2}') result correct: {"a": 1, "b": 2} run the GROUP_CONCAT query ...
0
votes
1answer
26 views

Accessing nested array in json blob in PostgreSQL

I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following: { "id" : "5", "data" : "[[1,2,3],[2,3,4],[4,5,6]....] } My query needs ...
-1
votes
2answers
44 views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
18
votes
6answers
49k 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 ...
-2
votes
1answer
39 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
0
votes
0answers
56 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 ...
0
votes
1answer
12 views

What's best practices for changing a user's email in an application?

I'm working on a crud for an email application, and I'd like the ability to change "everything". Right now, however, it matches the JSON string to the Database based on the email address. Problem: ...
6
votes
2answers
493 views

Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

Frequently in Javascript you'll have something like [ 7,2, [6,7], 2,10 ] How would you query that structure with OPENJSON I would like this, 0, 7 1, 2 2, 6, 2, 7, 3, 2 4, 10 I'm having a hard time ...
0
votes
1answer
27 views

PostgreSQL: Find row with JSON object attribute in array

I have a postgres database which is set up like this: CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"...
0
votes
0answers
15 views

Database design for arrays - utilize relational database

I am trying to build an application, where users can create unlimited "parsing fields", that will contain either a: String Array id | name | type ------------------- 1 | name | string 2 | table |...
0
votes
1answer
29 views

Append JSON array to an object in PostgreSQL

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. +----+------------+----------+----...
0
votes
1answer
253 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
1answer
410 views

Update postgres array jsonb

I'm using postgres 9.6, and my question is about updating a jsonb array column. My table looks more or less like that: ID Name MyArray 5 John [{"key":1,"key2":a}] 6 Mike [{"key":4,"key2":b}] ID ...
0
votes
0answers
30 views

How to get json object from json array where one of it's value fulfill assumption

In mysql I have json array like this [{"amount": "53.00", "paid_at": 2019-05-10, "payment_date": "2019-05-16"}, {"amount": "53.00", "paid_at": false, "payment_date": "2019-06-16"}, {"amount": "53.00"...
6
votes
2answers
9k 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
27 views

Use jsonb array inside where IN or ANY in PostgreSQL

I want to select all products that have active shipments (those have one or more shipment_ids in shipments table). My product table has a jsonb column called data like this: { "name": "foo", ...
1
vote
2answers
225 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", "...
0
votes
1answer
82 views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
0
votes
1answer
303 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(...
0
votes
1answer
25 views

What is the best way to unpack JSON objects in SQL Server?

Part 2 of my foray into JSON, I'd like to add a user to multiple groups: insert an JSON array into a table. Ideally, the JSON would look like this: '{ "Email": "WMogh@starfleet.gov", "...
10
votes
1answer
32k views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count from ...
0
votes
1answer
44 views

What's the low-down on MariaDB JSON support?

Does MariaDB ever plan to have full JSON support or is it just going to be an alias forever? It's a bummer to set constraints definition in the table just to make it work properly. Application-level ...
0
votes
0answers
28 views

What is the best way to update a table from a JSON string?

I'm fiddling around with JSON and SQL, and I need to update a record based on a new JSON string. Here's what I've got, but to be honest, It seems like an awful lot of code: create procedure ...
1
vote
3answers
9k views

How to update complex jsonb column?

I have a table with following definition: create table json_test ( filter_data jsonb); and i insert to it values like this: '{"task_packets": [ { "state": "PROCEEDING", "...
0
votes
1answer
137 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 ...
2
votes
1answer
110 views

Return all rows containing specific value in json array

I have the following table: CREATE TABLE [dbo].[Journals]( [Id] [nvarchar](450) NOT NULL, [CorrelationId] [nvarchar](450) NOT NULL, [Version] [int] NOT NULL, [Type] [nvarchar](max) ...
1
vote
1answer
147 views

flatten JSON array into comma delimited list

I am trying to construct a SELECT statement which will take a JSONB column in the following format: { "Cities": [ { "Name": "Atlanta" }, { "Name": "London" }, { ...