Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [json-path]

JSONPath is a means of using XPath-like syntax to query JSON structures

JSONPath is a means of using XPath-like syntax to query JSON structures.

It is not a standard, but it does offer a means of querying JavaScript structures directly without needing to convert to or from XML.

JSONPath expressions always refer to a JSON structure in the same way as XPath expression are used in combination with an XML document.

Helpful links:

29 questions
35
votes
1 answer

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); INSERT INTO grp VALUES …
BartZ
  • 453
  • 1
  • 4
  • 4
14
votes
2 answers

jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"

Using: Postgres 14.2. Objective: To get a list of all distinct countries in my table. The column address is a JSONB column type and contains an array structured like: { "address":[ { "types":["route"], "long_name":"20203…
bob_cobb
  • 243
  • 1
  • 2
  • 5
12
votes
2 answers

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": "somevalue"}, {"name":"bla2", "otherdata": "somevalue2"}, {"name":"bla3",…
Kobus
  • 149
  • 1
  • 1
  • 5
8
votes
2 answers

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","updatedAt":"1571145323"} ] I want to update the…
kishram
  • 83
  • 1
  • 1
  • 5
5
votes
1 answer

How to obtain the path to the match of a JSONPath query in PostgreSQL 14?

Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON: select jsonb_path_query( $$ { "id": "test", "data": [ { "$": "ref1" }, { "$":…
ralien
  • 153
  • 5
3
votes
1 answer

Update all instances of key inside nested JSON array

I'm trying to update a complex JSON document stored in a table in CockroachDB. The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer without user functions or explicit loops will…
JRL
  • 31
  • 5
3
votes
3 answers

Is it possible to remove a JSON array element by value in MySQL?

In MySQL, it's possible to remove an array element by index: SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`; +------------+ | result | +------------+ | ["a", "c"] | +------------+ Is it possible to remove by value instead - in this case…
Marcus
  • 390
  • 1
  • 4
  • 15
2
votes
0 answers

like_regex vs jsonb_build_object performance

I'm working with Postges 14.8 and using JSONB to store data. The queries that I'm working with perform filtering and JOIN'ing based on stuff in this JSONB column named data. We need to perform JOIN's to get the data we need. The setup is simple. We…
MRzeczkowski
  • 21
  • 3
2
votes
1 answer

json_array_elements returns empty dataset in postgres

I was trying to read elements from Json array inside another array that is stored on Postgres database, but my result set was always empty. My table looks like below: Sample Json from…
Karthika
  • 23
  • 4
2
votes
1 answer

How to construct a FOR JSON that converts a TOP 1 record into a json object (instead of a json array)

I want to use a JSON_QUERY with a SELECT TOP 1 query such that the resultant json has that top 1 record in object form, instead of in table form? For example, the following query (live demo): CREATE TABLE Trees ( [Id] INT, [Type]…
HeyJude
  • 467
  • 7
  • 18
2
votes
1 answer

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 fixed; sometimes its index can be 1, 2, or 3. I…
Gregorio Meraz Jr.
  • 31
  • 1
  • 6
1
vote
1 answer

Add key and value to elements of a jsonb array where the key does not exist yet

I have a table tbl with the following structure: CREATE TABLE tbl ( org text , data jsonb ); The data in the jsonb field is an array structured in the following way: INSERT INTO tbl VALUES ('SOMETHING' , '[ { "type": "XYZ", …
sharknado
  • 23
  • 1
  • 5
1
vote
1 answer

json_modify on condition

As in my previous question, I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify I'm used to using .modify with XPATH to be able to modify XMLs on the fly. For example, if I have an XML like the…
Rushabh Mehta
  • 251
  • 2
  • 7
1
vote
1 answer

Enumerating JSON arrays with OPENJSON

I've been working on converting some of my XML based stored procedures to JSON, but I'm running into an issue. With OPENXML, I had access to mp:id, so when I was parsing an XML, I could access an element and its position in the XML in my query. I'd…
Rushabh Mehta
  • 251
  • 2
  • 7
1
vote
1 answer

PostgreSQL Convert JSONB array to string

I am trying to flatten a nested JSONB object into a string so that I can run regex on it. To retrieve the relevant text fields, I'm using SELECT jsonb_path_query_array(posts.content, 'strict $.**.text') FROM posts which returns a jsonb (array).…
Sentient
  • 111
  • 1
  • 1
  • 3
1
2