Questions tagged [json-path]

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

Filter by
Sorted by
Tagged with
-1 votes
0 answers
31 views

How to select jsonb attribute in different paths in separate rows

I have a table with a jsonb column. For one of the requirements, I have to select 2 attributes - code and partyid - from 3 different paths each, but the paths for them are in pairs. For each polnum, ...
  • 101
0 votes
1 answer
47 views

[Resolved]: How to define the query for group by when using jsonb_query_path functions in the SELECT

I am trying to define the following GROUP BY query and need some help. I am using PostgreSQL DB table with a jsonb column select count(*), policynumber from ( select 'My ...
  • 101
0 votes
1 answer
47 views

PostgreSQL - jsonb - How to get the datatype for value in query with jsonpath

In PostgreSQL using jsonb column, is there a way to select / convert an attribute with actual datatype the datatype instead of getting it as a string object when using jsonpath? I would like to try to ...
  • 101
0 votes
0 answers
34 views

How to get parent node based on jsonpath in PostgreSQL

I am trying to figure out a way to extract information from huge json value in a PostgreSQL jsonb column and need some help with below situation. Not sure, if it can be achieved. The json is about ...
  • 101
0 votes
1 answer
99 views

How to select all nested values at a particular level in a json structure in a JSONB type column in postgresql?

In a postgresql database, I have a table with 2 columns: Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312 Column2 is of type JSONB and contains multiple rows of ...
0 votes
1 answer
60 views

How do I query inside a JSONB column when all values have the same json-like structure?

In a postgresql database, I have a table with 2 columns: Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312 Column2 is of type JSONB and contains multiple rows of ...
1 vote
1 answer
994 views

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 ('...
3 votes
1 answer
540 views

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 ...
  • 31
0 votes
1 answer
285 views

Specify range of Json array from different column

Mysql 8 makes it possible to extract part of an array saved in a JSON column as shown below: SELECT jcol->>'$[1 to 5]' FROM `db`.`table` which 1 and 5 are boundaries of the desired range of ...
4 votes
2 answers
4k views

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":[ {...
  • 143
1 vote
1 answer
532 views

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 ...
1 vote
1 answer
168 views

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 ...
3 votes
1 answer
595 views

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": &...
  • 131
0 votes
1 answer
444 views

How to extract a JSON object subset in Postgres (>12)

I have the following structure and query: SELECT jsonb_path_query_array(jsonb '{"subscores": {"score_a": 2, "score_b": 3, "score_c": 4}}','$.subscores.* ? (@>...
  • 183
0 votes
1 answer
60 views

how to get result value JSON and send result Format JSON

I have Query for result JSON but I need result like this: result:[ "B":{1628424359816,35}, "A":{1628424359816,1}, "B":{1628424380816,63}, "...
0 votes
1 answer
2k views

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'...
  • 101
2 votes
1 answer
326 views

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 ...
  • 413
-1 votes
1 answer
1k views

How to filter and get specific keys from JSON objects array

I have a JSONB field containing a value in this structure: [ { "type": "small", "name": "Josh", "greeting": "Hello ...
  • 99
1 vote
1 answer
1k views

PostgreSQL - How do I get all the values from a JSONB search on a nested object?

Postgres 12 with the following jsonb field I need to dig into: {"count":894, "limit":100000, "units":"lin", "observations": [ {"date":&...
2 votes
2 answers
5k views

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 | +------------+ | ["...
  • 327
0 votes
1 answer
984 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 ...
2 votes
1 answer
1k 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 ...
1 vote
1 answer
1k 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(((...
0 votes
1 answer
621 views

Does the SQL/JSON Path specification allow accessing the property of a filter expression's result

I noticed a difference in the evaluation of a SQL/JSON path expression between Oracle and Postgres and I wonder which one is correct. Consider the following JSON structure: { "key1": {"id":1000, "...
6 votes
2 answers
8k 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","...
  • 63
11 votes
2 answers
25k 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&...
  • 141