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

Index on json list

I have an attribute (ids) defined as jsonb in Postgres that contains a list of values: CREATE TABLE test.id_data2 ( key bigint NOT NULL, ids jsonb, CONSTRAINT tag_data2_pk PRIMARY KEY (...
23
votes
1answer
41k views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups. Given this example table: CREATE TABLE grp(d JSONB NOT NULL); INSERT INTO ...
2
votes
2answers
3k 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
50 views

Index for finding attributes in large JSONB array

Based on this original question and the follow up question from 2015. Suppose I have a jsonb column data within a table tracks. This column holds an array with multiple keys e.g. Artist and Title in ...
-1
votes
0answers
17 views

Join MariaDB table on JSON array to text field

Table crons has a JSON field called surveys which contains a JSON array of UUIDs like: [ "65f42c322ea442099612cad251be1dac", "43788fe207b143dc9eac4da38c036af6" ] Table1 (crons)...
0
votes
0answers
10 views

Most efficient method to import bulk JSON data from different sources in postgresql?

I need to import data from thousands of URLs, here is an example of the data: [{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{...
0
votes
1answer
53 views

Postgres Table toast size is too large [closed]

I want to store some information as JSON in the postgres table. Our application is using a platform. When they started business we store all the data in our db. But now as the application grows we ...
2
votes
2answers
1k views

ERROR: invalid GeoJson representation

the GeoJSON Spec show, { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } } But when I wrap that in ...
1
vote
2answers
33 views

Zip array with values from JSON

Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {&...
23
votes
2answers
42k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
5
votes
2answers
14k views

Find rows containing a key in a JSONB array of records

I'm trying to query for a key present in an array of objects. This structure: column jdata {"name": "Somedata", "array": [ {"name":"bla1", "attr&...
0
votes
2answers
561 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": "...
1
vote
2answers
23 views

Postgres 11 JSONB - How to get all distinct values of a field across all elements

I have a table called Shipments: CREATE TABLE shipments ( shipmentId numeric, metadata jsonb ); Inside the metaData column there can exist an array of JSON objects named stops which ...
0
votes
0answers
17 views

Merging two two nested JSONs in Postgresql

I have two jsons as follows JSON 1 - { "2021-03-11": [ { "14:30:00": 3000 } ], "2021-03-14": [ { "14:30:00": 130 } ] } JSON 2 - ...
0
votes
1answer
50 views

How to get a JSON array without JSON_ARRAYAGG in MySQL

I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT ...
0
votes
0answers
20 views

How can I set a JSON value to a boolean in SQLite?

I know that SQLite doesn't have a boolean data type, but for a JSON column I'd like to set the value to a real boolean instead of a 0/1 faux-boolean, but SQLite always seems to set it to an int. ...
0
votes
1answer
31 views

postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like ...
-1
votes
2answers
63 views

Get distinct values for all columns for all tables from specific schema

I would like to get all distinct values for all columns in each table of a specific schema. First, I summarised all my tables/columns in a new_table (myschema.dico) CREATE TABLE myschema.dico AS ( ...
0
votes
1answer
23 views

Add jsonb key/value pair with uuid

I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it. What I am trying to add is a key id with a uuid value This is how my data looks right now: { ...
0
votes
0answers
40 views

Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details. Illustrating artificial query is: with data as ( select 5 as some_value from ...
1
vote
0answers
10 views

Editing GeoJSON parsing code to parse JSON code instead in SQL

Apologies if this is an easy question to some, I have spent hours trying to figure this out and it has me stumped. So, I know GeoJSON takes the following format: { "type": "...
0
votes
1answer
679 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...
0
votes
1answer
37 views

JSON in MySQL : get data from an object within a range of keys

What I have: A mysql table, say table1 table1 contains two columns viz. id and data id is int but the twist is data is JSON type and its keys are comparable the table1 contains only one row (for the ...
0
votes
1answer
297 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 ...
1
vote
1answer
61 views

Is the size limit of a json field before or after compression in Postgres?

The size limit of a json field is 1GB (source: this StackOverflow answer): json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB. I ran some ...
2
votes
1answer
321 views

How to “insert into select from” skipping exceptions

1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle. I will need to report on JSON ...
0
votes
1answer
936 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
0
votes
0answers
8 views

Preserve tab and line break while insert JSON string to Document in MongoDB

I'm inserting new document to MongoDB as following: db.crawl_api_config.insertOne({ apiHost: "logbasex.github.io", apiUrl: "20221", contentType: "Blog", ...
0
votes
1answer
2k views

How to import json table into PostgreSQL table?

I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data: [{ "token": "a978f9793a594d11860e9cf7962996d7", "name": "vehicle.moving", "description": "...
1
vote
1answer
34 views

Local JSON file vs MySql database [closed]

I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from ...
2
votes
1answer
6k views

Unicode error with \u0000 on COPY of large JSON file into Postgres

I'm using Postgres 9.4 on Ubuntu 14.04. I have a 30GB JSON file that I'm trying to COPY into Postgres. But I keep getting the following error: COPY <table>(comment_jsonb) FROM '<json file&...
5
votes
3answers
5k views

Can SQL Server 2016 extract node names from JSON?

I want to extract a complete list of the node names and their paths from any arbitrary, well-formed JSON document stored as nvarchar value in SQL Server 2016. Is there a reliable way to do this? For ...
0
votes
1answer
2k views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...
1
vote
1answer
641 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
2
votes
0answers
64 views

check if a list of strings in a jsonb contains (or not) an array of strings

Given a table "bgp"."interconnect": Query 1 SELECT array_to_json(array_agg(netelements)) FROM ( SELECT jsonb_object_keys("bgp"."interconnect"."pulse&...
0
votes
0answers
28 views

How to run query to convert undefined JSON fields into a row dynamically

Related to this answer I found a solution above to get undefined JSON files into rows. However, I need to run query twice so I modify the query to run it once. I tried to run the query returned from ...
-1
votes
1answer
22 views

Read specific fields from Postgres jsonb

I have data like {"name": "a", "scope": "1", "items": [{"code": "x", "description": "xd"}, {"code": &...
1
vote
0answers
69 views

EAV vs JSON/XML column approach to store complex user form data in a database? [closed]

We are working on an internal tool which will essentially be a complex, power-user oriented form app. It is not a dynamic field app - users can't define their own fields. Form field types: The form ...
0
votes
1answer
24 views

JSON Creation Issue

I'm new to PostgreSQL database. Although I've good experience on Oracle Database. I'm facing issue while generating JSON from Postgres. I've tried to generate the same output but not able to achieve ...
14
votes
3answers
17k 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 ...
0
votes
1answer
554 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: ...
0
votes
1answer
920 views

How to filter a value in json field type on Postgres 9.2?

My json field data is like this: {"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
0
votes
0answers
25 views

Using GIN index for array and single value in the same key

We have table: create table last_games(id int not null primary key, name text not null, json_value jsonb); And filled with next values: insert into last_games values (1, 'Tournament1', '{"...
1
vote
1answer
100 views

Dynamically update JSONB rows - POSTGRESQL

I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field. The node we are trying to move, however, is not ...
0
votes
1answer
388 views

How to convert the JSON date with to_date function

How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date? That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
0
votes
1answer
931 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 ...
0
votes
0answers
23 views

Why does json_equal use so much PGA memory and how to avoid it?

I have an Oracle 19c database and a schema with two tables (let's call them L and R). They have the same structure: Two VARCHAR2 columns I'll call T and J. The second one has an IS JSON STRICT ...
1
vote
1answer
984 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: ...
0
votes
1answer
113 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
0
votes
1answer
86 views

(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres

So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects. How I ...

1
2 3 4 5
11