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
1answer
15 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}...
2
votes
1answer
30 views

Merging one-to-many and one-to-one relation in the same JSON object in postgresql

LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0 My tables are as follows: => \d ops; Table "public.ops" Column ...
1
vote
2answers
46 views

Output a table as well-formed, indented JSON

I have a table dictionary and its structure is: create table dictionary ( id integer, first_level_key character varying(50), key_name character varying(50), key_value_en character varying(...
1
vote
0answers
57 views

How to get value by key from unstructured json column in Postgres 11

I have jsonb column with unstructured json. It can contain any combination of keys and arrays. I need to find if the value of key "Grade" contains the specified string. I managed to do that in ...
1
vote
1answer
60 views

Postgres: Filtering large jsonb arrays coming from a sub-query

I have a table called external_data in Postgres designed with two columns : +----+---------------------------------------------+ | id | data | +----+------------...
0
votes
0answers
14 views

Struggling with json functions when argument is result of select

I can find a plethora of examples for json_populate_recordset when the argument of json_populate_recordset(base anyelement, from_json json) is a text string such as: select "values", "Type", "Name" ...
2
votes
1answer
36 views

Sql Server - Build a json array of integer from an aggregate

I want to use an aggregate to build a simple JSON array like this LuckyNumbers array : [{ "id": 1, "name": "Josian", "LuckyNumbers": [581, 777] }, { "id": 2, "name": "Paul", "...
0
votes
1answer
39 views

Postgres fetch exact ROW using JSON column

I have records in table like below format. Table struture CREATE TABLE test.orders ( id serial NOT NULL, info json NOT NULL, value varchar NULL, CONSTRAINT orders_pkey PRIMARY KEY (id) ); If I ...
1
vote
0answers
28 views

MySQL 5.7 - JSON - Searching array of KV objects

Version: MySQL 5.7.25, GCP CloudSQL. I'm looking to store Twilio SMS delivery status updates in MySQL. I wanted to try using JSON for this to store one record per SMS, but then have a JSON column to ...
1
vote
0answers
24 views

Json_agg slow down the performance

I have a database, simulate a structure of a document, like this table document: -------------------------------------------------- | Id | ------------------------------------------------- | 1 |...
1
vote
1answer
33 views

postgresql count null and blank value from jsonb column

In following JSON structure, [ ["a", 921], ["b", ""], .. ... ["c", null], ] how to count occurrence of blank value "" null value
0
votes
0answers
20 views

Insert multiple values with associations from json

Say we had a schema like this: create table person ( id serial primary key, first_name text ); create table vehicle ( id serial primary key, name text not null, person_id int unique not ...
-1
votes
1answer
44 views

converting json to columns

I have the following json structure { "products": [ { "description": "Horse shoes", "productid": 1, "orders": [ { "customerid": 101 }, { "customerid": 102 }, { "customerid": 103 } ...
0
votes
0answers
18 views

How to build simple jsonb object out of multiple arrays in postgres?

If I have three (or more) Postgres arrays with the same number of elements, and I want to build a multi-level JSONB object out of them—how can I do this? Example: create temp table tbl1 as select ...
0
votes
1answer
31 views

How to return JSON objects in tabular format using T-SQL?

I am storing student data by group (or class) and week in the following format: where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can ...
1
vote
1answer
22 views

Loop and array and sub array [closed]

I am working on a script that needs to loop an array and loop a sub array. I want to take in rows from a table (that piece works fine) that look like: invoice | item inv1 | it_A inv1 | it_C ...
0
votes
0answers
19 views

Indexing an attribute of the elements of an array of another array in a jsonb field

I have the following document that is stored in a jsonb field in a postgres 11 DB. { "bundles": [ { "id": "9c9c1b73-b058-3f22-a48b-725ce39eb861", "items": [ ...
0
votes
1answer
26 views

Json append results in error of - “No operator matches the given name and argument types”

clusters_v2=# UPDATE cluster SET "clusterCALogs" = "clusterCALogs" || '["test"]'::jsonb WHERE "clusterId" = 9; ERROR: operator does not exist: json || jsonb LINE 1: ...ATE cluster SET "clusterCALogs" ...
0
votes
2answers
33 views

mysql > why replace command is not working on strings coming from json encoded?

Say in my wordpress db I've got a post_content column containing strings like this: href=\"http:\/\/dev.example.com\/example\/wp-content\/uploads\/2019\/07\/my.pdf\" The above is a portion of string ...
1
vote
0answers
21 views

merging many jsonb objects using aggregate functions in PostgreSQL?

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL ...
0
votes
2answers
111 views

Efficient key value store in Postgres

I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
1
vote
1answer
32 views

How to store JSON data from APIs and be able to search efficiently

I am displaying documentation using Swagger, but the documentations are long so I made a side menu and split up the data in the database. Splitting it up, it allows me to search for specific areas and ...
0
votes
0answers
7 views

How to get all variables for the current plpgsql function?

Is it possible to get all the variables of a PL/pgSQL function (including input parameters and DECLAREed variables)? My goal is to collect these variables into a jsonb object with each variable name ...
1
vote
1answer
32 views

json_agg with dynamic 0-level key

I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this... { "a": { "id": 1, "b":{ "c1": { "...
0
votes
1answer
56 views

PostgreSQL row as jsonb “array”, not “object”

We are trying to emulate a proprietary DB, which has "global primary keys", with PostgreSQL. That is what we would have liked to do (for many rows of many tables): select 'some_table' as table_name, ...
0
votes
1answer
18 views

Are “column names” optimized in PostgreSQL JSONB jsonb_agg(*)?

Using this SELECT: select 'some_table' as table_name, jsonb_agg(t.*) as content from some_table t; If some_table had 1000 rows, are the "column names", which are repeated for each row in "content", ...
0
votes
1answer
42 views

Oracle JSON ADT in Collection

Why is the table() operator not working? create or replace type tmp_011040_t2 as table of (JSON_OBJECT_T) NOT PERSISTABLE; declare l_objects tmp_011040_t2; begin l_objects := tmp_011040_t2(); ...
1
vote
1answer
95 views

Update dynamic column names using keys from jsonb argument

I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
1
vote
1answer
25 views

PostgreSQL: joining on jsonb array

Using this contrived example structure: Base table data { "uid": "b12345", "nested": ["n12345", "n34567"] } Nested table data [ { "uid": "n12345", "message": "Hello world" }, { "uid": "...
2
votes
1answer
91 views

postgres jsonb query performance is decreased by order by

I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 ...
2
votes
1answer
40 views

Build a single JSON value from two jsonb columns in two different tables

I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id. I am trying to build one aggregated JSON value from these jsonb columns. Lets say table A ...
1
vote
2answers
442 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","...
0
votes
0answers
18 views

Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items ...
0
votes
0answers
165 views

BigQuery - export JSON with null columns

I have table in BigQuery with structured data - hence to export it I need to use JSON format. Default BQ export to JSON behavior (not to say "a bug") is to omit all columns with null values. But in my ...
2
votes
1answer
273 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
80 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 ...
-4
votes
1answer
137 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 ...
0
votes
1answer
54 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
268 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
2answers
96 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
2answers
63 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
248 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
268 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 ...
0
votes
1answer
25 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
67 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(...
0
votes
1answer
104 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)),'$....
2
votes
1answer
29 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
83 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
151 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 ...
-2
votes
1answer
52 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 ...

1 2 3 4 5 9