Questions tagged [json-path]
JSONPath is a means of using XPath-like syntax to query JSON structures
28
questions
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)...
- 443
12
votes
2
answers
40k
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&...
- 151
8
votes
2
answers
19k
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":[
{...
- 183
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","...
- 83
4
votes
1
answer
2k
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": &...
- 143
3
votes
1
answer
1k
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
3
votes
3
answers
12k
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 |
+------------+
| ["...
- 378
2
votes
1
answer
3k
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 ...
2
votes
1
answer
248
views
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 table:
{"...
- 23
2
votes
1
answer
884
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 ...
- 447
1
vote
1
answer
7k
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
('...
- 23
1
vote
1
answer
2k
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 ...
- 251
1
vote
1
answer
648
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 ...
- 251
1
vote
1
answer
13k
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'...
- 111
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(((...
- 113
1
vote
0
answers
107
views
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 ...
- 11
1
vote
1
answer
2k
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":&...
- 13
0
votes
1
answer
724
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 ...
- 149
0
votes
1
answer
2k
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 ...
- 105
0
votes
2
answers
229
views
Postgres like_regex with variables
I want to query a JSONB array using a regex filter. Following the docs, I should be able to do:
WHERE jsonb_path_exists(
data, '$.value[*] ? (@ like_regex $foo flag "i")',
...
- 109
0
votes
1
answer
2k
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 ...
- 105
0
votes
1
answer
574
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 ...
- 105
0
votes
1
answer
94
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
957
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, "...
user1822
0
votes
1
answer
330
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 ...
- 103
0
votes
1
answer
1k
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 ...
- 103
0
votes
1
answer
886
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.* ? (@>...
- 243
-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