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.

Filter by
Sorted by
Tagged with
140 votes
8 answers
453k views

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, ...
Christoph's user avatar
  • 1,593
59 votes
7 answers
174k 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 ...
Yanick Rochon's user avatar
25 votes
2 answers
83k 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 ...
Rabi C Shah's user avatar
8 votes
1 answer
24k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
spa's user avatar
  • 213
12 votes
2 answers
41k 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&...
Kobus's user avatar
  • 151
9 votes
2 answers
9k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
Christoph's user avatar
  • 1,593
72 votes
9 answers
127k 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
AliBZ's user avatar
  • 1,807
7 votes
1 answer
2k views

Inconsistent statistics on jsonb column with btree index

I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
Mad Scientist's user avatar
34 votes
2 answers
81k 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":...
Kokizzu's user avatar
  • 1,373
7 votes
3 answers
21k views

Pushing or appending to JSON array in PostgreSQL 9.4

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION add_array(...
genichm's user avatar
  • 179
5 votes
1 answer
21k views

Search for nested values in jsonb array with greater operator

Here is the table definition (simplified): CREATE TABLE documents ( document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, data_block jsonb NULL ); Sample values: INSERT INTO documents ...
Ryu's user avatar
  • 53
4 votes
1 answer
2k views

Why does a JSON element return text when it's not text?

When I try to access a JSON element using ->>'elementName' I get a texttype. SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2) FROM ( VALUES ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2); ...
Evan Carroll's user avatar
  • 63.7k
1 vote
2 answers
5k views

Unpacking JSON into 14 columns without crosstab() and tablefunc?

In a Postgres 12 database, I have a simple schema containing a JSON column. The JSON will always contain an object, and inside the object there are always 14 keys like so: CREATE TABLE json_unpacking ...
blong's user avatar
  • 115
0 votes
2 answers
1k 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", "...
Kobus's user avatar
  • 151
49 votes
2 answers
81k views

Replace multiple columns with single JSON column

I am running PostgreSQL 9.3.4. I have a table with 3 columns: id name addr 1 n1 ad1 2 n2 ad2 I need to move the data to a new table with a JSON column like: id data 1 {"name": "n1&...
AliBZ's user avatar
  • 1,807
25 votes
6 answers
127k views

MySQL: Return JSON from a standard SQL Query

I have read about JSON objects and the JSON object type. I only want to do a select and it return JSON. I do not necessarily want to store a JSON object. Serialization per se it not my question. ...
johnny's user avatar
  • 612
18 votes
1 answer
27k views

Loop through key/value pairs of a jsonb object in postgresql function

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object. create or replace function myFunction (input jsonb) returns jsonb as $$ BEGIN // ...
JaneL's user avatar
  • 183
12 votes
2 answers
17k views

PostgreSql JSONB SELECT against multiple values

I have a very simple JSON table which I populate with some sample data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}'); INSERT INTO ...
jgm's user avatar
  • 305
10 votes
2 answers
13k views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
Antonio's user avatar
  • 201
8 votes
2 answers
12k 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","...
kishram's user avatar
  • 83
7 votes
1 answer
3k 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 ...
jgm's user avatar
  • 305
6 votes
4 answers
11k views

Passing variable as parameter to json_query function

I am getting error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal ...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it. ...
Arvind Mishra's user avatar
5 votes
1 answer
2k views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
Blender's user avatar
  • 75
5 votes
1 answer
4k views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
Ken Li's user avatar
  • 153
5 votes
5 answers
8k 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 ...
Thomas's user avatar
  • 161
2 votes
3 answers
21k 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", "...
ArtemP's user avatar
  • 49
2 votes
1 answer
3k views

Index by subkey inside subkey inside array in jsonb

I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example: d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ] f492b26d-...
stackdave's user avatar
  • 201
2 votes
2 answers
4k views

json functions: double quotes added when going through a variable

Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go ...
Tarcaird's user avatar
1 vote
2 answers
15k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
virtual void's user avatar
1 vote
1 answer
2k views

How to sum the values ​of a json column filtered with regex?

How to sum the values ​​of a json column filtered with regex? Columns: A.name: Varchar with name product B.products: JSON with time active By product Query: select A.name as product, sum(((...
Luis Henrique's user avatar
34 votes
1 answer
77k views

Query JSON array of obejcts against multiple values

I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups. Given this example table: CREATE TABLE grp(d jsonb NOT NULL)...
BartZ's user avatar
  • 443
17 votes
2 answers
39k views

Does MariaDB support native JSON column data type?

I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version? CREATE TABLE example (names JSON); As far ...
Omranic's user avatar
  • 271
11 votes
5 answers
35k views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
antsyawn's user avatar
  • 255
10 votes
1 answer
13k views

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
Vakarelov's user avatar
  • 103
9 votes
2 answers
15k views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
thomaskeefe's user avatar
9 votes
1 answer
2k views

Do long names for jsonb keys use more storage?

Consider the example: Two tables foo and bar, each with a jsonb column. For foo, there are a million rows where the value of the jsonb is [{"a":123}]. For bar, there are a million rows where the ...
davidtgq's user avatar
  • 739
8 votes
2 answers
7k 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 to ...
coterobarros's user avatar
8 votes
1 answer
25k views

PostgreSQL update JSONB without jsonb_set

Would there be any drawbacks to using a normal update statement to update a json(b) column like so: update "events" set "properties" = '{"type":"graph"}' Instead of using the jsonb_set function ...
Pieter-Jan Vandenbussche's user avatar
7 votes
1 answer
24k views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": "<url>"...
TSK's user avatar
  • 73
6 votes
1 answer
17k views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get data ...
vibby's user avatar
  • 123
6 votes
3 answers
7k views

Use trigger to synchronize columns with fields in json column on insert or update

I'm a bit of a database/postgres beginner,so bear with me. If I have a table, something like this. CREATE TABLE testy ( id INTEGER REFERENCES other_table, name varchar(128) PRIMARY KEY, ...
kag0's user avatar
  • 163
5 votes
2 answers
4k views

How can I implement full-text search on a single value in a JSONB column?

I have a table that stores a conversation between two people. The data will look something like this: CREATE TABLE foo AS SELECT $$[ { "user": 1, "timestamp": 1, "message": "First message" }, ...
ndbroadbent's user avatar
5 votes
1 answer
4k views

PostgreSQL converting undefined JSONB fields into a row dynamically

I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance: From CREATE TABLE v(id,jsonb) AS VALUES ...
Cyberguille's user avatar
5 votes
1 answer
6k views

How to select sub-object with given keys from JSONB?

How to extract / get / select a "sub object" from a json(b) object in Postgres? There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
AlexanderF's user avatar
5 votes
2 answers
8k views

Create JSON object from recursive tree structure

Having this simple many-to-many self-referential structure. An item owns other items through the joins table: CREATE TABLE items ( item_id serial PRIMARY KEY , title text ); CREATE TABLE ...
Ben's user avatar
  • 179
5 votes
2 answers
567 views

Find Ancestry From JSON

I have a hierarchy that looks like this: As JSON in TSQL it is this: declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", ...
Michael Green's user avatar
4 votes
1 answer
10k views

Update nth element of array using a WHERE clause

I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this: { "name" : "...
Arash's user avatar
  • 175
4 votes
1 answer
2k views

possible to reference a jsonb_array_length in a where clause

I am just starting to use some of the jsonb functionality in Postgres 9.4 (would love to move to 9.6) . I have the following query: SELECT queryable_type, jsonb_array_length(as_data->'...
timpone's user avatar
  • 647
4 votes
1 answer
14k views

Storing special characters (German, French, Spanish) in SQL Server result in weird strings

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße" But this also happens for other characters: "König-...
Adam's user avatar
  • 245
3 votes
2 answers
1k views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
RockNinja's user avatar
  • 683