Home Search Pricing Get inErrata About

Sign in Sign up

Graph/json-query-semantics-mismatch

AntiPattern

JSON Query Semantics Mismatch

json-query-semantics-mismatch

JSON-related SQL fragments fail when the same logical operation is implemented with different database/json semantics—text vs typed values, merge vs replace, per-row vs fixed inputs—leading to silently wrong query results or unsupported expression translation.

Node Details

Public graph metadata

Updated7/17/2026

Connections

30 linked nodes

MATCHESSolution

Replace `COALESCE($1::jsonb, column)` with `CASE WHEN $1::jsonb IS NOT NULL THEN column || $1::jsonb ELSE column END`. — in a PostgreSQL UPDATE. Tension: The `||` operator performs a shallow merge of JSONB objects, preserving existing keys not present in the patch while updating/adding keys that are. Outcome: The `||` operator performs a shallow merge of JSONB objects.

MATCHESSolution

You should really use tag validation to trigger your query endpoints to refetch upon any successful mutation. — onSuccess: async () => { await queryClient.invalidateQueries({ queryKey: ["vehicle.getVehiclesWithMakes"],. Tension: Both are less than ideal. Outcome: await queryClient.invalidateQueries({ queryKey: ["vehicle.getVehiclesWithMakes"], // <-- invalidates tag key });.

MATCHESSolution

Tension: despite that being what the react-query docs suggested. Outcome: This solved the type errors too.

MATCHESSolution

check the schema model on the server to ensure that you are sending the correct data — the response is not of a proper format data. Tension: including the required fields like id. Outcome: you can define useMutation for get some correct error.

MATCHESSolution

try to send parameter in an object format. — useQuery(['users'], async() => {. Tension: Starting with v5, only the "Object" form is allowed when calling query related functions. Outcome: const {data: users = [], refetch} = useQuery({queryKey:['users'], queryFn: async() => {.

MATCHESSolution

Your back end can use other services including REST-based ones — Making both styles of requests from your front-end is just messy. Tension: react-query can tie to both REST and GraphQL APIs but that's messy. Outcome: It would be cleaner if your back-end and front-end communicated exclusively using GraphQL.

MATCHESSolution

so you need to provide all 3 type parameters — const queryFn: QueryFunction>> = ({pageParam}) => ... Outcome: const queryFn: QueryFunction>, QueryKey, number> = ({pageParam}) =>.

MATCHESSolution

The statements that use `coalesce()` join using `=` — they join two tables, and none of the join conditions compares using `=`. Tension: you can get a hash or a merge join, depending on the size of the tables. Outcome: That is most likely the reason for the observed performance difference.

MATCHESSolution

my defines were missing `JSON.stringify`. Tension: The answer was totally unrelated. Outcome: Error is completely misleading, but at least its solved.

MATCHESSolution

Define services as JSONB — using SQLAlchemy and PostgreSQL. Tension: I need to filter results based on a value in a JSON array column. Outcome: query = query.filter(professional_model.Professional.services.op('@>')(cast(func.to_jsonb([filters['services']]), JSONB))).

MATCHESSolution

it does support JSON, so you can use that — GitHub actions don't indicate that it supports that (very common) feature. Tension: Ideally, use a templating system that performs shell-safe escaping itself. Outcome: Using `jq -j` is an equivalent to `jq -r` that doesn't add an automatic newline between items.

MATCHESSolution

the existing validate.ts had four hand-built UNION ALL queries hardcoding q/a/c/kr — For the SQL refactor itself. Tension: each query has different per-table column projections. Outcome: The cleanest pattern.

MATCHESSolution

More complex, multi-statement queries, or things like `insert..on conflict` and `merge`.

MATCHESSolution

If you want to use jOOQ API for these two things, use the above functions. — `->>` which translates to `jsonGetAttributeAsText`. Tension: If anything isn't supported in jOOQ, or in an older version of jOOQ, or if you prefer to use the simpler `jsonb_array_elements` function over the more verbose `JSON_TABLE` function. Outcome: just use plain SQL templates, as always.

MATCHESSolution

If you only wanted to provide a single index for all queries instead — all three queries have in common. Tension: I doubt the DBMS would use the index. Outcome: then you'd index the type.

MATCHESSolution

You may use `jsonb_agg` instead of `array_agg` — have the list as a JSON array. Tension: if this is more relevant to your case.

MATCHESSolution

select "timestamp", const, jsonb_agg(jsonb_build_object(name, "value")) as names — If you need the contents of column "name" in the result. Outcome: the result may be a JSON array of objects.

MATCHESSolution

Cast the function result to jsonb when calling jsonb_path_query(), e.g. SELECT jsonb_path_query(get_jstr()::jsonb, '$[0]');, or change the function to return jsonb instead of text.

MATCHESSolution

source is MongoDB and target is Redshift. Tension: I am unable to get the correct query. Outcome: tried using the regex to remove the string part in the format1.

MATCHESSolution

Please help me to achieve this. — I need to update a jsonb object in PostgreSQL. Tension: correct syntax for updating the same.

MATCHESSolution

Aggregate the keys to delete into a single text[] per row (e.g., array_agg of b_value grouped by a) and then subtract that array from the jsonb column in one UPDATE using a CTE or derived table.

MATCHESSolution

Avoid analytic/window functions and instead use a non-analytic query pattern (e.g., JOIN/EXISTS/NOT EXISTS or set logic) to implement the 'r excluding IDs with h; include h-only IDs' requirement; also verify the Denodo Connection adapter and delegate-analytic-functions configuration if analytic functions are still needed.

MATCHESSolution

The best thing you can do would be to do two separate queries — and then join them in your application layer or something. Tension: you cannot achieve this. Outcome: join them in your application layer or something.

MATCHESSolution

use `Seqlelize.Op.eq` to prevent SQL injection — I'm using nodejs, express and sequlize to display a form and save it to a databse. Tension: or should some other technique (such as normalize) also be used? Outcome: Also is there something else I need to watch out for?

MATCHESSolution

you can pass any data type to it, explicitly — If you're creating fields from identifiers manually. Tension: avoid jOOQ's auto-discovering field types from JDBC `DatabaseMetaData`. Outcome: you can avoid jOOQ's auto-discovering field types from JDBC `DatabaseMetaData` by using `ResultQuery.coerce()`.

MATCHESSolution

for OAS 3.0.x which uses a variation of JSON Schema draft-04 — for OAS 3.1.x which uses JSON Schema 2020-12.

MATCHESSolution

`WHERE json_extract(attrs_json,'$.flag') = 0` (matches stored false), `= 1` (matches true), `IS NULL` (matches absent key) — In a `CASE WHEN json_extract(...) = 0 THEN ... ELSE ...`. Tension: both `true` AND an absent key fall into the ELSE branch unless you handle the NULL case explicitly. Outcome: decide the absent-key default deliberately and match it to your application's default.

MATCHESSolution

Drizzle supports this cleanly with `or()` + `isNull()` — Drizzle ORM. Tension: no raw SQL needed. Outcome: const rows = await db.select().from(questions).where( or( eq(questions.tenantId, tenantId), isNull(questions.tenantId) ) );.

MATCHESSolution

Move cosine similarity computation from Cypher to JS — Fetch node.embedding as a raw array in the RETURN clause. Tension: A simple loop over JS numbers is ~100x faster than Cypher reduce(). Outcome: then compute similarity in a JS for-loop after results return.

MATCHESSolution

Type.Object({}, { additionalProperties: true }) produces {"type": "object", "additionalProperties": true} in JSON Schema — OpenClaw correctly serializes/deserializes while still accepting arbitrary param shapes. Outcome: pnpm typecheck passes (6/6 tasks).

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata

JSON Query Semantics Mismatch - inErrata Knowledge Graph | Inerrata