Skip to main content

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.

116 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
0 answers
379 views

Full-text search index across columns and tables

I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
ennui's user avatar
  • 81
3 votes
0 answers
898 views

Why is PostgreSQL not using composite Index on JSONB Fields?

Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example: SELECT id from table where (j1->>'f1')::int = 1 AND (...
Sleepster's user avatar
  • 131
2 votes
2 answers
2k views

Unexpected newlines in json output in Postgresql

I'm squashing a datastructure to JSON objects using the PostgreSQL Json functions, however, there are unexpected newlines in the output. Basically, it creates JSON objects and put them in a list. This ...
Rob Audenaerde's user avatar
2 votes
0 answers
1k views

Is it worth using a JSON column type, even if it's only used for simple serialized strings or integers?

I have a table with an unidexed value column of VARCHAR(1024), and I've used my ORM to automatically convert it to and from JSON. The entire reason for this is I want it to be able to store any data ...
Peter's user avatar
  • 121
2 votes
0 answers
3k 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&...
nskalis's user avatar
  • 21
2 votes
0 answers
2k views

How to Use LOAD DATA to import JSON data and specify date data type on import

I'm importing JSON data in sql and would like like to convert human readable date data to yyyy-mm-dd format. Here's the flow: I'm using the following json file stored under /home/name/json_data/...
Cookie Monster's user avatar
2 votes
1 answer
708 views

Postgres - detect change in JSONB column value from 'null' to some value across rows

I have to work with a table that looks like this: The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } ...
Uzumaki Naruto's user avatar
1 vote
1 answer
75 views

MariaDB: inconsistent SELECT results involving JSON

When executing the following statments on MariaDB 10.6.14 SET @json_1 = JSON_OBJECT('id', 'name'); SET @json_result_1 = JSON_OBJECT('test', @json_1); SELECT @json_result_1; SET @json_result_2 = ...
Andreas Hinderberger's user avatar
1 vote
1 answer
39 views

Checking if a specific date is closed in a JSON data stored within a text field in MariaDB

Problem Description I'm currently working on a complex stored procedure that involves a table called "price_options_test". Within this table, there is a text field named "close_days&...
Khuzema's user avatar
  • 13
1 vote
0 answers
145 views

MongoDB Compass JSON Document Failed Validation

Apologies in advance if I am asking this question in the wrong place. I have read a number of similar questions and responses on this topic but many seem outdated and don't seem to fit my issues. I am ...
CG3's user avatar
  • 11
1 vote
0 answers
34 views

Allow procedure to get multiple (but not defined) number of input variables

I have a serie of statements like this one: SELECT `Article`.`id`, `Article`.`title`, CONCAT('[', GROUP_CONCAT(DISTINCT CASE WHEN (`junction_article_author`.`authorRank` IS NULL AND `Authors`.`...
fauve's user avatar
  • 111
1 vote
0 answers
2k views

Choosing between Entity-Attribute-Value or JSONB representations

I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table ...
Daniel Díaz Carrete's user avatar
1 vote
1 answer
522 views

Postgres convert to columns from string json

I am running postgresql 14.4. I have a table with 2 fields: id data_product(varchar) --- ------------ 1 [{"product_id":1,"product_code":"A1","...
hungtv's user avatar
  • 11
1 vote
1 answer
636 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
chris's user avatar
  • 11
1 vote
3 answers
722 views

Cannot import JSON file into MySQL

I am trying to import a JSON file into a MySQL database. It is FAA data of all the airports in the US. https://services6.arcgis.com/ssFJjBXIUyZDrSYZ/arcgis/rest/services/US_Airport/FeatureServer/0/...
Paul's user avatar
  • 143
1 vote
0 answers
3k views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
Kohjah Breese's user avatar
1 vote
1 answer
1k views

Set difference for jsonb

The hstore type in Postgres provides an operation to calculate the set difference (EXCEPT) of two records. Is there something similar for jsonb? The best I've got is SELECT * FROM jsonb_each('{"a&...
jo-so's user avatar
  • 53
1 vote
0 answers
225 views

Storing user defined forms and their data

I'm writing an application that would have a feature similar to Google Forms. In this feature, some end users of the system will have the ability to design custom forms. Those forms will subsequently ...
hashemi's user avatar
  • 111
1 vote
0 answers
436 views

Generic update of "normal" columns with data from JSON

I am trying to do a "generic" insert/update of table columns using JSON. There are tons of info how to update a JSON column, but I have found none that updates several normal columns in a ...
sibert's user avatar
  • 176
1 vote
1 answer
604 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
krames's user avatar
  • 111
1 vote
0 answers
847 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 - ...
ash's user avatar
  • 11
1 vote
0 answers
21 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": "...
user_map123's user avatar
1 vote
0 answers
337 views

Should I use Postgres' JSON columns for additional related data of different types and not yet known?

I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys. The tbl_main is the most frequently written to table, as in it receives new ...
kmindi's user avatar
  • 111
1 vote
0 answers
623 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
blazerix's user avatar
  • 111
1 vote
0 answers
69 views

Can I assign an index to an exploded array?

I'm doing a LATERAL EXPLODE on an array in Hive, is there a way of reliably assigning a row number based on the array element? It looks like calling row_number() on the results of the LATERAL EXPLODE ...
PhilHibbs's user avatar
  • 539
1 vote
1 answer
2k 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 ...
SundayProgrammer's user avatar
1 vote
0 answers
23 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 ...
PhilHibbs's user avatar
  • 539
1 vote
0 answers
713 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,...
matthewmturner's user avatar
1 vote
0 answers
158 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 , ...
Ramakrishna's user avatar
1 vote
0 answers
412 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 ...
zoran's user avatar
  • 111
1 vote
0 answers
803 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 ...
jlindenbaum's user avatar
1 vote
0 answers
1k views

Json_agg slow down the performance

I have a database, simulate a structure of a document, like this table document: -------------------------------------------------- | Id | ------------------------------------------------- | 1 |...
pilo's user avatar
  • 11
1 vote
1 answer
2k 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":...
Brian Brownton's user avatar
1 vote
0 answers
114 views

How to read jsonb or json in postgresql spi?

How can I read a json/jsonb field in a PostgreSQL server side function? I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain ...
tinlyx's user avatar
  • 3,600
1 vote
0 answers
156 views

Joining on nearest timestamp in MongoDB

MongoDB version: 3.2.11 Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To ...
iskandarblue's user avatar
1 vote
0 answers
204 views

Get JSON with where condition mysql

I have JSON value in column : INSERT INTO `bt_product` (`_Price`) VALUES ('{\"245\":\"10\",\"366\":\"5\",\"688\":\"1\"}'), ('{\"26\":\"1\"}'), ('{\"32\":\"1\"}'), ('{\"52\":\"1\"}'), ('{\"42\":\"1\"}'...
demo test's user avatar
1 vote
0 answers
56 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 ...
HBat's user avatar
  • 111
1 vote
0 answers
434 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 ...
Try431's user avatar
  • 111
1 vote
0 answers
126 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 ...
Jules's user avatar
  • 23
1 vote
1 answer
1k 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 ...
Min2j's user avatar
  • 11
1 vote
0 answers
409 views

Is it possible to return empty response if there is no records in DB

Is it possible to return empty objects if there is no records in db? db.query(' SELECT * from users where type = $1 and category = $2 limit 8', [admin, filltime], function(err, result) { ...
Schüler's user avatar
  • 453
1 vote
0 answers
132 views

What data store should I use for storing and querying a billion small (~2kb) JSON documents?

I'm trying to figure out which database to use for storing and querying on ~1 billion JSON documents about the size of 2KB each. About 2 TB of data. Nature of JSON documents: 3 types of documents (...
Akshit Khurana's user avatar
1 vote
0 answers
3k views

How do you concatenate JSONB columns in a PostgreSQL query to produce a JSONB object?

I am trying to produce a specific json object output from rows of a single column of jsonb and I'm stuck. Before I drop into PL/pgSQL, I wanted to ask the question here. I need to concatenate a ...
Jay Cummins's user avatar
1 vote
0 answers
14 views

JSON format for time-logging

Hi I'm creating a very simple application that logs activities. Right now I'm thinking I would just do {"Activity":[ {"ActivityType":"Sleep"}, {"time_begin": someunixtime, "time_end":...
user2250649's user avatar
1 vote
0 answers
192 views

Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

I have two tables: hub and huboperator hub table is defined as CREATE TABLE public.hub ( parent_hub_id integer, ancestors jsonb, descendants jsonb, name character varying(1024), id integer ...
Himanshu Gupta's user avatar
1 vote
0 answers
970 views

How to set the trigger when field(contain json value) is updated?

I have one table in which there is one field called info.This field hold json value. I want to create trigger on this field.I want to split this json format string into record I mean I want to insert ...
Kreya's user avatar
  • 155
1 vote
1 answer
931 views

Perform SELECT using WHERE IN clause on JSON Object in MySQL

I have a json column in MySQL which contains a list of items for eg. "style": {"style1", "style2", "style3", "style4", "style5"} I want to ...
girish's user avatar
  • 111
0 votes
2 answers
23 views

Query nested json columns in postgresql

I want to return Germany and UK from the below json column/string. { "id": 1, "name": "phone", "components": [ { "id": 1, "...
Jackie Ndinwa's user avatar
0 votes
1 answer
56 views

Json or separate column?

I'm developing a table structure to store the physical activity data and users can save their progress. The activities belong to multiple programs. like beginner program suppose be like having 1000 ...
Vidya L's user avatar
  • 101
0 votes
1 answer
21 views

Oracle returns invalid number of rows on json_table

When I run following query on Oracle 19, it returns 27 record instead of 30, and I cannot understand why, as I am expecting 30 records: select * from json_table('{"values":[[1,120674124,...
Amir Pashazadeh's user avatar