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

Full text search with json field in SQLite

Is it possible to have full text search in SQLite along using json? In SQLite json is stored as TEXT (JSON1). Full text search requires creation of virtual table (full text) but I don't know how to ...
0
votes
0answers
69 views

Splitting columns and getting result as a JsonArray in Postgresql

I have a table like this: Time | id/type | value 1 A/a 10 2 A/b 15 3 A/c 8 4 A/b 2 I need to get a Json Array in the format: [[Time, value for ...
0
votes
0answers
63 views

Is it possible to use json_extract inside json_keys in mysql?

I am trying to get a particular json key from nested keys.So is it possible to use json_extract inside json_keys. something like this : select json_keys(select DEVICE_ID,inform ->>'$.[0].MO[0]."1"[0]....
0
votes
0answers
93 views

Memory exhausting when accessing large files of JSONB columns

I have a table with one of the column data jsonb, it has proper index on the column for filter condition but some times query is running too long and some times DB crashing because of out of memory ...
1
vote
2answers
28 views

How do I return a json object of my database table [closed]

I'm creating a web client that uses data from my database to plot markers on a google map. So my collumns are id, name, address, lat, lon, description. My client gets the data from a route in my ...
1
vote
1answer
900 views

How to select subset JSON with PostgresQL?

I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string ...
0
votes
1answer
40 views

Query to update the JSON that does not contain a specific key

I want to query a table that has JSON data. The data of the column detail in the table details are as follows. id | 1 detail | { myCompany: [{ email: 'company@company.com', inProfit: true }], ...
0
votes
1answer
56 views

PostgreSQL query and shape in json array?

I have a json array stored in my postgres database in jsonb column. The json data is facebook ad data and look like this: { "cpc": "1.1", "cpm": "11.1", "cpp": "11.1", "ctr": "0.11", "ad_id": "11", "...
0
votes
1answer
102 views

LEFT JOIN LATERAL conditions and JSONB

I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object. First, a ...
0
votes
1answer
849 views

Compress JSON String Stored in PostgreSQL, such as MessagePack?

JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string: {"0.000295":1544.2,"0.000324":1050,"0.000325":...
1
vote
0answers
8 views

Defining external table on JSON with an @ sign in an element

I need to define a Hive external table onto a JSON file that has @ signs in its elements, e.g. { "data": { "@type": "person", "name": "Phil", "job": "Programmer" } } This works: create external ...
0
votes
1answer
974 views

Update a JSON column with WHERE Clause in JSON Column Postgres

I am having trouble writing a WHERE clause to update a JSON column in Postgres database: Given a table of the form: create table Test_Table ( clusterId INT, owner VARCHAR(50), data ...
2
votes
1answer
447 views

Extract and combine multiple values from a jsonb column

I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null. fiddle Setting up schema: create table jsonb_test (test ...
1
vote
0answers
334 views

Nested data in postgres

I have a CSV with columns of various data types, including what i would interpret as nested data. The nested data comes in many forms. For example: [13,11,23,18,18,21,23,16,8,20,22,18,20,17,13,10,8,...
2
votes
2answers
44 views

Is using a JSON data type here appropriate?

I am using MySQL 8.0 and I have an ACL table, which has two columns int UserID and tinyint GroupID. They both form a composite primary key. A user can be in many groups, however there are only a few ...
0
votes
1answer
34 views

Filter rows by jsonb key mask

1. I have table UserData with following schema: ID - bigint UserID - bigint Data - jsonb 2. Data column jsonb has following structure: date - contain timestamp text - contain description photo_* - ...
0
votes
1answer
17 views

postgres jsonb return specific paths of json object

Consider the following jsonb object: { "track": { "segments": [ { "location": [ 47.763, 13.4034 ], "startTime": "2018-10-14 10:05:14", "HR": 73 }, { ...
0
votes
1answer
946 views

How to Save JSON Data in MySQL?

I receive a series of JSONAPI (https://jsonapi.org/format/) documents as a response to an API call that I make in my application. I need to save the API responses to my database so I can reference it ...
-1
votes
1answer
23 views

Subquery with a JSON property

I have a working query which extracts Events of type 'Searched for Flights' from the following table: SELECT "public"."events"."id" AS "id", "public"."events"."event" AS "event", "public"."events"."...
1
vote
0answers
68 views

JSON formatting using group by and table valued function

I want this query to group based on ClientContactGroupID. select cg.ClientContactGroupID,ClientID,ContactGroupName , gcon.ClientContactID,gcon.ClientContactGroupID , gcon.FirstName , ...
1
vote
1answer
416 views

Postgres btree partial index on jsonb array -> array expression seems to get corrupted for larger tables in version 9.5.x

I am looking into indexing of jsonb attributes and I am seeing something suspicious with Postgres 9.5.x but not in higher versions. Below is what I did that triggered the strange query errors. It ...
0
votes
2answers
452 views

Looping through JSON and insert data to Postgres DB

Below is how i have my data at the moment. How would i achieve in postgres with Postgresql to get all those "class_name" and "id" values and store them on their own rows in the database. I can store ...
0
votes
1answer
21 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
100 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
3answers
77 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
247 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
386 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
36 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" ...
3
votes
1answer
1k views

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

I want to use an aggregate to build a simple single-dimensional JSON array of scalar values, like the LuckyNumbers array in the example below: [{ "id": 1, "name": "...
0
votes
1answer
54 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
391 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
235 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
838 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
-1
votes
1answer
48 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
1answer
38 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
26 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
24 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
203 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
304 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 ...
4
votes
1answer
652 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
2k 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
36 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 ...
1
vote
1answer
202 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
385 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
67 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
120 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(); ...
2
votes
1answer
1k 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
54 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
680 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 ...
3
votes
1answer
226 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 2
3
4 5
11