SQL Server 2016 JSON Field – Arithmetic Overflow Error with WHERE Clause

Here is a small example to prove my point:

CREATE TABLE some_data (
    data hstore
);

INSERT INTO some_data
VALUES ('a=>bla'),
       ('access=>bla'),
       ('access=>user');

Now taking your JSON as an example, we can do something like this:

WITH condition_elements AS (
    SELECT key, 
           ARRAY(SELECT jsonb_array_elements_text(value->'authorized_keys')) AS value 
      FROM jsonb_each('
        {
         "matricule":   {"authorized_keys":["12"],
                         "unauthorized_keys": ["1", "2", "20"]},
         "departement": {"authorized_keys": ["it"],
                         "unauthorized_keys":["finance", "account"]},
         "access":      {"authorized_keys" : ["superuser", "user", "login"],
                         "unauthorized_keys" :  ["web"]}
        }'::jsonb)
)
SELECT data->key AS key, 
       value, 
       data->key=ANY(value) AS matches
  FROM some_data, 
       condition_elements;

The actual output is to describe what to expect from above:

 key  │         value          │ matches 
──────┼────────────────────────┼─────────
      │ {superuser,user,login} │ 
 bla  │ {superuser,user,login} │ f
 user │ {superuser,user,login} │ t
      │ {12}                   │ 
      │ {12}                   │ 
      │ {12}                   │ 
      │ {it}                   │ 
      │ {it}                   │ 
      │ {it}                   │ 

If we move the necessary part to a condition, it will work nicely:

-- add some more data for testing
INSERT INTO some_data
VALUES ('departement=>bla,access=>bla'),
       ('departement=>it,access=>bla'),
       ('departement=>it,access=>login');

WITH condition_elements (... as above ...)
SELECT *
  FROM (
        SELECT data, bool_and(data->key=ANY(value)) AS full_match
          FROM some_data, condition_elements
         GROUP BY data
       ) t
 WHERE full_match;

                  data                  │ full_match 
────────────────────────────────────────┼────────────
 "access"=>"login", "departement"=>"it" │ t
 "access"=>"user"                       │ t

Notes:

• It is always better to present real code in your question - like actual CREATE TABLE statements, a syntactically correct JSON snippet and a query that actually can run.
• For translating JSON arrays into PostgreSQL arrays, I used here a technique that is described in detail in Erwin Brandstetter's excellent answer.
• Check the documentation about the different JSON(B) operators and functions. It contains quite a lot of useful stuff.

This is caused by one or two things, much like the error says:

1. A config change was made to the AG
2. The primary restarted
3. The ghost cleanup low watermark was set

The first two are fairly obvious as to why this would be an issue. The 3rd is not.

Since secondary replicas automagically remap read-committed isolation level to the snapshot isolation level a few things need to kick in. One of those things is row versioning, since after all SI is an optimistic concurrency.

Row versioning depends on the version store rows to be available on the secondary. If the low water for ghost cleanup is changed, then the secondary may need to cleanup records that are currently used for queries being satisfied by the version store which have a timestamp lower than the new ghost cleanup low watermark. When this is the case, those versions will be cleaned up and any SI datasets that required them will no longer be valid. That's when this error will surface for the session for that dataset.

select distinct some_column 
from some_table oo (nolock) 
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

If this query runs quickly, I don't see why it should error out like that as I doubt your low watermark is changing that rapidly. Thus, I'm going to assume this takes some time to run. Having the appropriate indexes, not using a long transaction, and up-to-date statistics will help with this query. Additionally, the NOLOCK hint is completely ignored on the readable secondary, you may already know that and it may just be code re-use which is fine but I wanted to point it out anyway.

We are considering using read-intent to resolve the issue, do you think that will resolve the problem?

ApplicationIntent is only used for two things in general with AOAGs:

1. read only routing (ROR)
2. to access AG Replicas that are set as secondary mode read-intent only.

This will not solve the underlying problem. IMHO you have two options, make the query run faster (if it isn't already) or open up a support ticket with MS. The reason I say open a ticket is this query should not constantly fail 24/7 and the issue should be looked into.