Given the following jsonb array, I want to filter it only for certain objects.

[
  {
    "id": "123"
  },
  {
    "id": "456"
  },
  {
    "id": "789"
  }
]

I can use the function jsonb_path_query_array with the path strict $[*] ? (@.id == $idToSearch[*]) and a variables parameter of {"idToSearch":["123","456"]} to filter by the id parameter. The logic being: * $[*] breaks out the array * ? is a filter predicate... * ... where the id property is equal to ... * ... any value in the idToSearch array variable. I get this straightforward result:

[
  {
    "id": "123"
  },
  {
    "id": "456"
  }
]

If however, I invert the == in the path query to strict $[*] ? (@.id != $idToSearch[*]) then instead of getting the correct result of

[
  {
    "id": "789"
  }
]

instead I just get all the objects again. As a workaround, I can use a negated equals strict $[*] ? (! (@.id == $idToSearch[*])) to get the correct result. **Why is the query not returning the correct result?** Is this a bug? If so, what's the cause and has it been filed? If it's not a bug, where am I going wrong in the path query, and what would be the correct way to express it? ____

create table test (
  _id int,
  items jsonb
);

insert into test values (1, '[
  {
    "id": "123"
  },
  {
    "id": "456"
  },
  {
    "id": "789"
  }
]');

select
  jsonb_path_query_array(
    items,
    'strict $[*] ? (@.id != $idToSearch[*])',
    '{"idToSearch":["123","456"]}')
from test;

**dbfiddle**

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 have one table that is partitioned based on one of our columns and then we JOIN rows in different partitions. Each partition has the same amount of rows in our tests - around 8 mln. We'd like to have one place to INSERT rows and partitioning improved our performance. I'm using a gin index like this: CREATE INDEX ix_tab_data ON public.tab USING gin (data jsonb_path_ops); And a btree index for pagination: CREATE INDEX ix_tab_created_timestamp ON public.tab USING btree (created_timestamp DESC); They're then inherited by partitions. Most queries look something like this:

SELECT User.data->>'Name' AS User_Name
, User.data->>'Surname' AS User_Surname
, User.data->>'Gender' AS User_Gender
, Address.data->>'StreetName' AS Address_StreetName
 FROM public.tab_user AS User
 JOIN public.tab_address AS Address ON Address.data @> jsonb_build_object('Id', User.data->'AddressId')
 WHERE
  Address.data @> jsonb_build_object('StreetName', 'Test')
 ORDER BY User.created_timestamp DESC
 LIMIT 500
 OFFSET 1000;

Query plan:

Limit  (cost=10000001275.74..10000001829.33 rows=500 width=136) (actual time=193.677..293.630 rows=500 loops=1)
  Buffers: shared hit=106258
  ->  Nested Loop  (cost=10000000168.54..149189116161.91 rows=125713639119 width=136) (actual time=0.333..293.527 rows=1500 loops=1)
        Buffers: shared hit=106258
        ->  Index Scan using tab_user_created_timestamp_idx on tab_user user  (cost=0.43..9720087.72 rows=8556337 width=205) (actual time=0.012..7.544 rows=8728 loops=1)
              Buffers: shared hit=8754
        ->  Bitmap Heap Scan on tab_address address  (cost=168.11..15972.39 rows=14692 width=373) (actual time=0.032..0.032 rows=0 loops=8728)
              Recheck Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @> jsonb_build_object('StreetName', 'Test')))
              Rows Removed by Index Recheck: 0
              Heap Blocks: exact=1502
              Buffers: shared hit=97504
              ->  Bitmap Index Scan on tab_address_data_idx  (cost=0.00..164.44 rows=14692 width=0) (actual time=0.030..0.030 rows=0 loops=8728)
                    Index Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @> jsonb_build_object('StreetName', 'Test')))
                    Buffers: shared hit=96002
Planning:
  Buffers: shared hit=2
Planning Time: 0.175 ms
Execution Time: 293.705 ms

GIN indexes are used and query plans look fine. From what I saw online this @> jsonb_build_object method is recommended by most. However when we've done tests by sending many queries we found they don't perform as fast as we'd like to, so we've started working on improvements. Since jsonb_build_object creates a new JSONB and then we check for containment we've tried to use a json_path. So the query looked like this:

SELECT User.data->>'Name' AS User_Name
, User.data->>'Surname' AS User_Surname
, User.data->>'Gender' AS User_Gender
, Address.data->>'StreetName' AS Address_StreetName
 FROM public.tab_user AS User
 JOIN public.tab_address AS Address ON Address.data@>jsonb_build_object('Id', User.data->'AddressId')
 WHERE
  Address.data @? '$.StreetName ? (@ == "Test")'
 ORDER BY User.created_timestamp DESC
 LIMIT 500
 OFFSET 1000;

Query plan:

Limit  (cost=10000001273.23..10000001825.58 rows=500 width=136) (actual time=189.903..287.950 rows=500 loops=1)
  Buffers: shared hit=106258
  ->  Nested Loop  (cost=10000000168.54..148874820513.06 rows=125713639119 width=136) (actual time=0.207..287.849 rows=1500 loops=1)
        Buffers: shared hit=106258
        ->  Index Scan using tab_user_created_timestamp_idx on tab_user user  (cost=0.43..9720087.72 rows=8556337 width=205) (actual time=0.007..7.470 rows=8728 loops=1)
              Buffers: shared hit=8754
        ->  Bitmap Heap Scan on tab_address address  (cost=168.11..15935.66 rows=14692 width=373) (actual time=0.031..0.031 rows=0 loops=8728)
              Recheck Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @? '$."StreetName"?(@ == "Test")'::jsonpath))
              Rows Removed by Index Recheck: 0
              Heap Blocks: exact=1502
              Buffers: shared hit=97504
              ->  Bitmap Index Scan on tab_address_data_idx  (cost=0.00..164.43 rows=14692 width=0) (actual time=0.030..0.030 rows=0 loops=8728)
                    Index Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @? '$."StreetName"?(@ == "Test")'::jsonpath))
                    Buffers: shared hit=96002
Planning:
  Buffers: shared hit=2
Planning Time: 0.141 ms
Execution Time: 288.016 ms

Performance was the same as with jsonb_build_object. We've tried using like_regex instead of == in the json_path and surprisingly it was fastest. Current query looks like this:

SELECT User.data->>'Name' AS User_Name
, User.data->>'Surname' AS User_Surname
, User.data->>'Gender' AS User_Gender
, Address.data->>'StreetName' AS Address_StreetName
 FROM public.tab_user AS User
 JOIN public.tab_address AS Address ON Address.data@>jsonb_build_object('Id', User.data->'AddressId')
 WHERE
  Address.data @? '$.StreetName ? (@ like_regex "Test")'
 ORDER BY User.created_timestamp DESC
 LIMIT 500
 OFFSET 1000;

Query plan:

Limit  (cost=10050199374.23..10051799360.17 rows=500 width=136) (actual time=40.426..61.151 rows=500 loops=1)
  Buffers: shared hit=61138
  ->  Nested Loop  (cost=10046999402.34..402290157972457.19 rows=125713639119 width=136) (actual time=0.054..61.049 rows=1500 loops=1)
        Buffers: shared hit=61138
        ->  Index Scan using tab_user_created_timestamp_idx on tab_user user  (cost=0.43..9720087.72 rows=8556337 width=205) (actual time=0.008..6.836 rows=8728 loops=1)
              Buffers: shared hit=8754
        ->  Bitmap Heap Scan on tab_address address  (cost=46999401.91..47015169.46 rows=14692 width=373) (actual time=0.006..0.006 rows=0 loops=8728)
              Recheck Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @? '$."StreetName"?(@ like_regex "Test")'::jsonpath))
              Rows Removed by Index Recheck: 1
              Heap Blocks: exact=8744
              Buffers: shared hit=52384
              ->  Bitmap Index Scan on tab_address_data_idx  (cost=0.00..46999398.24 rows=14692 width=0) (actual time=0.003..0.003 rows=1 loops=8728)
                    Index Cond: ((data @> jsonb_build_object('Id', (user.data -> 'AddressId'::text))) AND (data @? '$."StreetName"?(@ like_regex "Test")'::jsonpath))
                    Buffers: shared hit=43640
Planning:
  Buffers: shared hit=2
Planning Time: 0.171 ms
Execution Time: 61.214 ms

All queries return the same rows but like_regex is fastest - which is counterintuitive. Around 16% of rows in public.tab_address have the StreetName we're looking for in our queries. The more JSONB filters in WHERE the bigger the performance difference. We are currently looking for an explanation of this behavior. From what I saw online, regex searches tend to be more resource-intensive. Maybe when combined with the jsonb_path_ops index, Postgres can optimize the path traversal and quickly identify relevant records before applying the regex filter? It seems like query #2 should be faster than a regex search. I guess the performance depends on how the jsonb_path_ops index handles equality checks versus pattern matching. Does someone have an idea why like_regex is faster than jsonb_build_object and == in json_path?

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", "otherdata": "somevalue"} ], "otherstuff": "stuff" } Now I do btree's on jdata->'name' or (jdata->'datetime')::cast and that works great. I also do json_path_ops where jdata->'array' @> '[{"name":"bla3"}]' works a real charm. My problem is the attr key can be in any of the objects in the array and I care about the record if the key is present, however the value can be nearly anything. Is there a way to query for this? Is there a way it can be indexed? I want to do jdata->'array' @> '[{"attr": ?}]' Or maybe the ? 'attr' can be used inside an array somehow? Currently I am thinking a trigger that scans for the key and then moves it to a header with a true or false or whatever, and then a normal btree will work. Is there a better way? I need to edit around 500k records at the average site to add this values. Please point me in a direction.

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 updatedAt key of all objects in that array using one query. I tried: update my_table set data = data || '{"updatedAt": "1571150000"}'; The above query added a new object within the array like the following:

[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"},
   {"updatedAt":"1571150000"}
]

I want the output like:

[
   {"id":"1","status":"test status1","updatedAt":"1571150000"},
   {"id":"2","status":"test status2","updatedAt":"1571150000"}
]

I also tried jsonb_set(), but that needs the second parameter to be the array index. I can't be sure of the count of JSON objects in the array. If this can be solved with custom functions, also fine.

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")',
  jsonb_build_object('foo', 'demo')
)

but I'm getting an error:

ERROR: syntax error at or near "$foo" of jsonpath input

I think it's because $ is a valid regex character? Here is some demo data:

INSERT INTO table_data (fields) VALUES
	 ('[{"value": "Sales"}]'),
	 ('[{"value": "CEO Staff"}]'),
	 ('[{"value": "Procurement"}]'),
	 ('[{"value": "CEO Staff"}]');

I wish to query all that have a value containing 'ceo'.

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: ~~~pgsql CREATE TABLE grp(d jsonb NOT NULL); INSERT INTO grp VALUES ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}') , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}') , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}'); ~~~ I found similar question (https://dba.stackexchange.com/questions/89971/postgresql-jsonb-select-against-multiple-values) and managed to achieve what I want on simple array using this query: SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar']; However, I can't make it work when when array contains JSON ***objects***: SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}']; Here is what I expect from my query: grp "First" -> customer "1" grp "Third" -> customer "5"

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: {"test":{"accountName":"OS1U","details":[{"status":{"statusCode":"Test Ready","modifiedDate":"2023-07-31T14:53:49Z"},"products":[{"productId":"0001","productName":"2GB","productDescription":"testproduct12"},{"productId":"0005","productName":"2GB","productDescription":"testproduct12"}]}]}} Here my requirement is to get all the id's from table which is having productId (0001,0005). I tried below sql: SELECT x.id FROM ( SELECT json_array_elements("json" -> 'test' -> 'details'->'products') AS products,"id" FROM table_test b ) x WHERE x.products ->> 'productId' in ('0001','0005'); But it always returns empty dataset. Can anyone please guide me to correct the above statement

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). I've tried naively casting the jsonb to ::text[] and then array_to_string, but that results in the following type error:

cannot cast type jsonb to text[]. null

I have also tried wrapping the result in a jsonb_array_elements_text, but that gives me a list of rows for _all posts_ instead of just one. Edit: Following this [answer](https://dba.stackexchange.com/a/54289/238642) , I've come up with

SELECT posts.post_id, plaintext.plaintext
FROM posts, LATERAL (
   SELECT string_agg(value::text, ', ') AS plaintext
   FROM   jsonb_array_elements_text(jsonb_path_query_array(posts.content, 'strict $.**.text'))
   ) plaintext;

In MySQL, it's possible to remove an array element by index:

SELECT JSON_REMOVE('["a", "b", "c"]', '$') result;
+------------+
| result     |
+------------+
| ["a", "c"] |
+------------+

Is it possible to remove by value instead - in this case the element(s) matching the string "b"? Based on the documentation, it's possible to find the index via JSON_SEARCH and then passing it to JSON_REMOVE, however, I was wondering if there's an expression/function to do it in a single function.

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"
    },
    {
      "$": "ref2"
    },
    {
      "engine": "dual",
      "fuel": "nuclear"
    }
  ],
  "owner": {
    "$": "ref3"
  },
  "bazaar": {
        "manager": {
        "$": "ref4"
      }
  }
}$$,
    'strict $.**."$"'
);

The returned results are:

"ref1"
"ref2"
"ref3"
"ref4"

However, what I need is a list of paths to these matches, i.e.,

"$['data']['$']",
"$['data']['$']",
"$['owner']['$']",
"$['bazaar']['manager']['$']"

How can this be accomplished? Context: a database contains jsonb values, where the $ sign is a placeholder for a value stored in a different table. The choice of $ is unfortunate, because it is also a part of JSONPath syntax, but this is a legacy system, so things have to stay this way. Let's focus on "ref4" from my sample query. In another table, ref4 points to {"name": "Balthazar", "occupation": "engineer"}. My goal is to substitute the references in the original structure with the objects they point to. In other words, the JSON will change from ... "bazaar": {"owner": {"$": "ref4"}} to "bazaar": {"owner": {"name": "Balthazar", "occupation": "engineer"}}. My intention is to write a function that will be called recursively, which looks for the $ entries and replaces them with the values from the other table, then returns the final JSON. In the same context, I'd like to find out if you can suggest better ways to accomplish the objective.

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 Query' as WhichQuery, jsonb_path_query(AnchorNode, '$.HdrRoleData[*].PartyID')#>> '{}' as PartyID, json_agg(json_build_object('Code', jsonb_path_query(AnchorNode, '$.Role[*].RoleData[*].Code')#>> '{}')) as Code, policynumber, lob as "LINE_OF_BUS" from ( select PolicyNumber, lob, jsonb_path_query(payload, '$.PolicyHdr.PolicyHdrPartyRole[*]') as AnchorNode from sch.fast_policymaster ) as my_select group by policynumber order by 1 desc ) as countalias When I run the above query, I get the below error SQL Error : ERROR: column "my_select.AnchorNode" must appear in the GROUP BY clause or be used in an aggregate function Position: 187 I also tried various PostgreSQL aggregate functions, but they also give an error similar to the one below SQL Error [0A000]: ERROR: aggregate function calls cannot contain set-returning function calls Hint: You might be able to move the set-returning function into a LATERAL FROM item. Can I do a GROUP BY in this situation? **Answer to Comment Question** No. But this is the first part of the whole process I am tying to get working. I am trying to compare the output from my inner query to output from another query from a different group that gets the same 4 attributes, where policynumber is key and for this query, each policy could have more than 1 partyids or codes, so my output shows all of that in separate rows. But the count of rows retrieved from my query doesn't match the count of rows retrieved from the other query, there is a difference of 2. But since the number of rows retrieved are more than 200K for each query, I was trying to do the above query where I will get the rows for each policy for my query, and do a similar outer query for the other group's query and on top of that do an outer query that will compare the two results to find out the difference. **Corrected SQL** select count(*), policynumber from ( select 'My Query' as WhichQuery, jsonb_path_query(AnchorNode, '$.HdrRoleData[*].PartyID')#>> '{}' as PartyID, json_agg(json_build_object('Code', jsonb_path_query(AnchorNode, '$.Role[*].RoleData[*].Code')#>> '{}')) as Code, policynumber, lob as "LINE_OF_BUS" from ( select PolicyNumber, lob, jsonb_path_query(payload, '$.PolicyHdr.PolicyHdrPartyRole[*]') as AnchorNode from sch.fast_policymaster ) as my_select ) as countalias group by policynumber order by 1 desc Thank you

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 avoid cast as well as -> and ->> type of construct since I have to select many attributes with very deep paths, I am trying to do it using jsonpath and * or ** in the path Is it possible to do it this way or must I use the -> and ->> for each node in the path ? This will make the query look complicated as I have to select about 35+ attributes in the select with quite deep paths. Also, how do we remove quotes from the selected value? This is what I was trying, but doesn't work to remove quotes from Text value and gives an error on numeric Select PolicyNumber AS "POLICYNUMBER", jsonb_path_query(payload, '$.**.ProdModelID')::text AS "PRODMODELID", jsonb_path_query(payload, '$.**.CashOnHand')::float AS "CASHONHAND" from policy_json_table the PRODMODELID still shows the quotes around the value and when I add ::float to second column, it gives an error SQL Error : ERROR: cannot cast jsonb string to type double precision Thank you

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 jsons which have this structure: { "a": [ { "a1": "2022-01-01", "a2": "2026-01-31", "a3": 1 } ], "b": [ { "b1": "J", "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9" } ], "c": [ { "c1-1": "2021-02-01", "c1-2": "2021-01-01" }, { "c2-1": "2021-04-01", "c2-2": "2021-03-01" } ] } I need to build a SELECT statement to return **only** the values of the b2 property from all rows of Column2. If it has just one row, then just '1231342138adc2fehj3j21321321321kjk1423j32k9'. If it has multiple rows, any value that it finds inside each jsonb inside each row. Any help is greatly appreciated. I tried:

SELECT *
FROM t
WHERE jsonb_path_exists(t.jsonColumn, '$.b[*].b2 ? (@ == "1231342138adc2fehj3j21321321321kjk1423j32k9")')

SELECT * returns the whole row of the table but I don't know how to instruct it to return just whatever value the b2 property contains (nested inside the json from the row) .

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 jsons which have this structure: { "a": [ { "a1": "2022-01-01", "a2": "2026-01-31", "a3": 1 } ], "b": [ { "b1": "J", "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9" } ], "c": [ { "c1-1": "2021-02-01", "c1-2": "2021-01-01" }, { "c2-1": "2021-04-01", "c2-2": "2021-03-01" } ] } I need to build a SELECT statement to return the row above giving the value of b2 (1231342138adc2fehj3j21321321321kjk1423j32k9) as an input parameter. Something like: SELECT * FROM table WHERE [Column2 contains b which contains b2 which has value '1231342138adc2fehj3j21321321321kjk1423j32k9'] ; OR just: SELECT * FROM table WHERE [value '1231342138adc2fehj3j21321321321kjk1423j32k9' exists in row from Column2 ] I just don't know how to give this instruction. Any help is greatly appreciated.

I have a table tbl with the following structure: ~~~pgsql CREATE TABLE tbl ( org text , data jsonb ); ~~~ The data in the jsonb field is an array structured in the following way: ~~~pgsql INSERT INTO tbl VALUES ('SOMETHING' , '[ { "type": "XYZ", "valueA": "500", "valueB": "ABC" }, { "type": "ABC", "valueA": "300", "valueB": "CDE" } ]') ; ~~~ I want to add a key valueC to elements of data where the object has a "type" key with a value of "XYZ". valueC's value will be an array of strings. The values of the array will depend on the value of the org column. I want to do this for all rows such that if a specific org is present, and the jsonb array in the data column contains an object with "type": "XYZ", then I get this result:

[
  {
    "type": "XYZ",
    "valueA": "500",
    "valueB": "ABC",
    "valueC": ["SOMETHING"],
  },
  {
    "type": "ABC",
    "valueA": "300",
    "valueB": "CDE",
  }
]

I also want to ensure this script only runs if valueC is not present in the object that matches the conditions, so it is not re-run during a migration/rollback unless needed. Here's what I have so far but it's not working when it does not find a result to the subquery and I can't figure out how to only run this if valueC does not exist: ~~~pgsql UPDATE tbl SET data = jsonb_set( data, '{data}', (SELECT jsonb_agg(elem ||'{"valueC":["SOMETHING"]}') FROM jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ') ) WHERE org = 'SOMETHING'; ~~~

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: ~~~pgsql { "address":[ { "types":["route"], "long_name":"20203 113B Ave", "short_name":"20203 113B Ave" }, { "types":["locality","political"], "long_name":"Maple Ridge", "short_name":"Maple Ridge" }, { "types":["administrative_area_level_3","political"], "long_name":"Maple Ridge", "short_name":"Maple Ridge" }, { "types":["administrative_area_level_2","political"], "long_name":"Greater Vancouver", "short_name":"Greater Vancouver" }, { "types":["administrative_area_level_1","political"], "long_name":"British Columbia", "short_name":"BC" }, { "types":["country","political"], "long_name":"Canada", "short_name":"CA" }, { "types":["postal_code"], "long_name":"V2X 0Z1", "short_name":"V2X 0Z1" } ] } ~~~ How can I filter this array of objects such that it only returns the value of "long_name" (e.g. Canada) for the array index if types contains "country"? Something like this is what I'm trying but, obviously, I want to return only the country and not the entire brand. SELECT * from brand where address::text ilike ANY (ARRAY['%country%'::text]); This query fails with: > ~~~none > ERROR: cannot extract elements from an object > ~~~ SELECT * from brand where exists ( select from jsonb_array_elements(address) e where (e ->> 'types')::text = 'country' ); Obviously, this is trivial to do in JS: address.filter((part) => part.types.includes('country')).long_name But I need my db to handle it. What's going wrong?

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 most likely work. The below sample code is also Postgres-compatible. Among other things that make the document complex are that it contains nested arrays and some redundant structures. The table and document are similar to the following:

> show create table foo;
  table_name |                      create_statement
-------------+--------------------------------------------------------------
  foo        | CREATE TABLE public.foo (
                 id INT8 NOT NULL,
                  data JSONB NOT NULL
              );

> insert into foo (id, data)
values(1, '{
    "foo": {
        "bar": {
            "bar": [
                {
                    "fields": [
                        {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "pink"
                        }
                    ]
                },
                {
                    "id": "ALL",
                    "fields": [
                          {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "white"
                        }
                    ]
                },
                {
                    "id": "1",
                    "fields": [
                          {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "green"
                        }
                    ]
                },
                {
                    "id": "ALL",
                    "fields": [
                                            {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "red"
                        }
                    ]
                }
            ]
        }
    }
}');

> select jsonb_pretty(data) from foo;
                     jsonb_pretty
-------------------------------------------------------
  {
      "foo": {
          "bar": {
              "bar": [
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "pink"
                          }
                      ]
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "white"
                          }
                      ],
                      "id": "ALL"
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "green"
                          }
                      ],
                      "id": "1"
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "red"
                          }
                      ],
                      "id": "ALL"
                  }
              ]
          }
      }
  }

How do I set the value for all instances of the key named color to an empty string ("")? The syntax for handling this on nested arrays eludes me.

Mysql 8 makes it possible to extract part of an array saved in a JSON column as shown below:

jcol->>'$[1 to 5]' FROM db.table

which 1 and 5 are boundaries of the desired range of elements in array. My question is how to specify the range by numbers from different column. Some thing like this:

jcol->>'$[table2.from to table2.to]' FROM db.table

As in my [previous question](https://dba.stackexchange.com/questions/304001/enumerating-json-arrays-with-openjson) , 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 following (example taken from SqlShack) Josef Brown jo0@adventure-works.com M Katie McAskill-White katie0@adventure-works.com Maddie Brown madhatter@adventure-works.com And I wanted to change the names of all users with user_id="2" to "Nat", I could write the following: xml.modify('replace value of (/users/user[@user_id=("2")]/fname/text()) with "Nat"') Now let's talk about JSON. Suppose I had the following JSON {"Users": [{"user_id":1,"fname":"Josef","lname":"Brown","email":"jo0@adventure-works.com","gender":"M"}, {"user_id":2,"fname":"Katie","lname":"McAskill-White","email":"katie0@adventure-works.com"}, {"user_id":2,"fname":"Maddie","lname":"Brown","email":"madhatter@adventure-works.com"}] } I want to do the same I did above, but with this JSON. How do I use json_modify to do this?

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 like to do something similar with OPENJSON. Here's an example DECLARE @JSON VARCHAR(MAX) = '[ {"Fruit":"Apple"}, {"Fruit":"Banana"}, {"Fruit":"Pineapple"} ]' SELECT Fruit FROM OPENJSON(@JSON,'$') WITH (Fruit VARCHAR(24) '$.Fruit') For each fruit, I'd like to have its position as well (i.e., 1 for apple, 2 for banana, etc). Does anyone know how to do this with OPENJSON?