JSON Custom Query

Filter and order supported modern JReviews queries with strict, model-aware JSON, and migrate legacy Custom Where / Order safely.

#Overview

JSON Custom Query is the recommended advanced-query format for supported modern JReviews pages and widgets. It lets you add model-aware filters and ordering without placing raw SQL in a setting.

Use the ordinary category, listing type, author, date, relationship, and ordering controls first. Add JSON only for requirements those controls cannot express. JSON where conditions can narrow the surface's eligible row set but cannot widen it; JSON ordering and the surface's limit can still change which eligible rows appear.

#Choose an Approach

Use JSON Custom Query wherever the setting is available. It accepts only the JReviews fields, relationships, operations, and context values documented in this guide.

Keep Legacy Custom Where / Order only for legacy Joomla modules, legacy WordPress widgets, legacy shortcode/module controllers, and the legacy Related Reviews renderer. Those settings contain SQL fragments and are not valid JSON.

#Supported Surfaces and Context

The setting is implemented on these surfaces:

• Listings, Comments, Media, and Calendar Site Widgets
• Listing and comment/review Custom List menu pages
• Related Listings on the modern listing detail page
• Related Comments on the modern listing detail page

Related Comments is a first-class Comment-model surface. Its relationship controls select comments related by comment author, commented-listing owner, or listing type. JSON then narrows those comments and can replace the selected comment order.

#Context-token matrix

Tokens are values supplied by the surface. Viewer and server-date tokens are distinct from listing-context tokens. Merely placing a Site Widget on a listing page does not supply listing_id or listing_owner_id.

The date tokens are current_year, current_month, and current_day, calculated by the server when the query is compiled. user_id is the authenticated person viewing the site—not the listing author or the administrator who saved the query. For an anonymous viewer, user_id is unavailable. A query that requires an unavailable token is rejected; the token never becomes 0 and its condition is never skipped.

#How Queries Compose

JSON filtering is added to the widget or page's regular filters:

1. JReviews applies the location's regular publication, access, and filtering rules first.
2. Each top-level JSON where condition is added with AND. An OR group applies only inside that added condition; it cannot bypass the regular rules.
3. If the JSON contains an order, it replaces the regular ordering setting. Without order, the regular setting remains active.
4. JSON uses a default limit of 100 when no smaller limit is already present. The widget or page can apply a smaller result limit afterward.

In practical terms, a JSON condition can narrow the available results, but it cannot make an otherwise hidden or excluded record eligible. Ordering and result limits still determine which matching records appear first.

The regular rules preserved by each supported location are:

JSON Custom Query does not add a new listing access policy. Your existing JReviews and CMS permissions continue to control access.

Regular selections such as explicit IDs, category, listing type, author, date window, media type, and Related Listings or Related Comments relationship rules stay active when JSON is applied.

When a non-random JSON order does not include the model's primary key, JReviews appends the primary key ascending as a deterministic tie-breaker. A JSON RAND() term uses random ordering instead.

#Query-System Reference

Use the tabs for syntax and setting-reference material. Surface selection, composition, migration, AI assistance, and verification remain outside the tabs because they apply to the migration as a whole.

{
    "where": [
        {"column": "id", "operator": ">", "value": 0}
    ]
}

{
    "column": "created_by",
    "operator": "=",
    "token": "user_id"
}

{
    "conditions": [
        {"relation": "field_data", "column": "featured", "operator": "=", "value": 1},
        {"column": "title", "operator": "LIKE", "value": "%featured%"}
    ],
    "logic": "OR"
}

{
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

{"column":"field_data.jr_city","operator":"=","value":"Boston"}

{"relation":"field_data","column":"jr_city","operator":"=","value":"Boston"}

#Verified Pattern Library

Every example uses the documented JReviews field and relationship names. Check the surface note before using a context token.

#Current viewer's listings

Valid for a Listings Site Widget, a Listing Custom List page, or Related Listings when the viewer is authenticated. It fails closed for an anonymous viewer.

{
    "where": [
        {"column": "created_by", "operator": "=", "token": "user_id"}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Exclude the current listing

Use only in Related Listings. That component already excludes the current listing; this example shows how listing_id is resolved and is useful when adapting a more complex condition.

{
    "where": [
        {"column": "id", "operator": "!=", "token": "listing_id"}
    ]
}

#Listings by the current listing owner

Use only in Related Listings. The query fails closed if the current listing has no available owner.

{
    "where": [
        {"column": "created_by", "operator": "=", "token": "listing_owner_id"}
    ]
}

#Listings created this month

Valid on Listing-model surfaces.

{
    "where": [
        {"column": "YEAR(created)", "operator": "=", "token": "current_year"},
        {"column": "MONTH(created)", "operator": "=", "token": "current_month"}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Custom field and rating relation

Valid on Listing-model surfaces. Replace jr_city with an existing listing custom field.

{
    "where": [
        {"relation": "field_data", "column": "jr_city", "operator": "=", "value": "Boston"},
        {"relation": "aggregates", "column": "user_rating", "operator": ">=", "value": 4}
    ],
    "order": [
        {"relation": "aggregates", "column": "user_rating", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Upcoming listing dates

Valid on Listing-model surfaces. Replace jr_eventdate with an existing date custom field.

{
    "where": [
        {"relation": "field_data", "column": "jr_eventdate", "operator": ">=", "value": "CURDATE()"},
        {"relation": "field_data", "column": "jr_eventdate", "operator": "<=", "value": "DATE_ADD(CURDATE(), INTERVAL 30 DAY)"}
    ],
    "order": [
        {"relation": "field_data", "column": "jr_eventdate", "direction": "ASC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Random featured listings

Valid on Listing-model surfaces.

{
    "where": [
        {"relation": "field_data", "column": "featured", "operator": "=", "value": 1}
    ],
    "order": [
        {"column": "RAND()"}
    ]
}

#Current viewer's comments

Valid for a Comments Site Widget, a comment/review Custom List page, or Related Comments when the viewer is authenticated. It fails closed for an anonymous viewer.

{
    "where": [
        {"column": "user_id", "operator": "=", "token": "user_id"}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Related Comments from the current listing owner

Use only in Related Comments. This JSON runs after the component's selected relationship rule and listing-type filter.

{
    "where": [
        {"column": "user_id", "operator": "=", "token": "listing_owner_id"},
        {"column": "listing_id", "operator": "!=", "token": "listing_id"}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Comments created this month

Valid on Comment-model surfaces.

{
    "where": [
        {"column": "YEAR(created)", "operator": "=", "token": "current_year"},
        {"column": "MONTH(created)", "operator": "=", "token": "current_month"}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Comment custom field relation

Valid on Comment-model surfaces. Replace jr_verified with an existing comment custom field.

{
    "where": [
        {"relation": "field_data", "column": "jr_verified", "operator": "=", "value": 1}
    ],
    "order": [
        {"column": "created", "direction": "DESC"},
        {"column": "id", "direction": "ASC"}
    ]
}

#Migration Guide

#1. Identify the actual surface and model

Record whether the target is a Site Widget, Custom List page, Related Listings, or Related Comments. Then select the correct model schema: Listing, Comment, or Media. Calendar uses the Listing schema.

Do not migrate a legacy module merely because a similar modern widget exists. Leave the legacy SQL on its legacy controller until you intentionally replace that placement with a modern Site Widget or component.

#2. Separate ordinary settings from advanced intent

Move categories, listing types, explicit IDs, author selections, relationship modes, date windows, and ordinary limits into the surface's normal controls. JSON should express only the remaining filtering or ordering requirement.

#3. Map to documented fields and relationships

• CMS listing title columns become title.
• CMS listing author columns become created_by.
• CMS listing creation-date columns become created.
• Listing custom fields become the field_data relation and their jr_... names.
• Listing rating/count data uses the aggregates relation.
• Comment custom fields use the Comment field_data relation.

Never carry table prefixes, table aliases, raw WordPress post_* names, joins, or SQL comments into JSON.

#4. Replace supported dynamic values

Use a token only where the surface matrix supplies it. Replace supported relative-date intent with the exact safe date expressions. Rewrite straightforward boolean logic as conditions and groups.

#5. Stop on unsupported intent

There is no automatic JSON equivalent for an arbitrary subquery, join, correlated query, database-specific expression, or unsupported function. Keep the old placement on its legacy controller, redesign the query using ordinary settings and allowlisted relations, or request a product enhancement. Do not invent a near-equivalent that changes who or what is selected.

#6. Validate expected behavior

Save the query through the administration form, then test authenticated and anonymous viewers as applicable. Check known included records, excluded records, order ties, empty results, page/widget limits, and every CMS or extension model in use.

#AI-Assisted SQL-to-JSON Migration

AI can help translate intent, but it must be constrained by the strict schema above. Use the prompt as an intake assistant: it must collect and confirm the required migration inputs before it attempts a conversion.

#Required inputs

• CMS: Joomla or WordPress
• Target surface: exact Site Widget, Custom List, Related Listings, or Related Comments
• Target model: Listing, Comment, or Media
• Legacy Custom Where value, or an explicit confirmation that it is empty
• Legacy Custom Order value, or an explicit confirmation that it is empty
• Ordinary filters and relationship settings that must remain active
• Known records that must be included, excluded, and first/last in the result

You do not need to identify context tokens. Once the exact surface is known, the assistant must derive its allowed tokens from the context-token matrix.

#Migration prompt

Convert the JReviews Legacy Custom Where / Order intent below to a
strict JSON Custom Query.

The required reference is:
https://www.jreviews.com/docs/v6/json-query-builder

Before conversion, confirm that you can access that reference and use
its context-token matrix and strict JSON grammar, including the model
fields, relations, operators, functions, date expressions, and limits.
If you cannot access the reference, output no JSON. State that a
reliable conversion cannot proceed without the reference material,
request that the user paste or attach the context-token matrix and the
relevant strict grammar/model reference sections, and wait. Never
reconstruct or guess the grammar.

Inputs:
- CMS: [Joomla or WordPress]
- Surface: [exact surface]
- Target model: [Listing, Comment, or Media]
- Legacy Custom Where: [paste exact value, or explicitly write "empty"]
- Legacy Custom Order: [paste exact value, or explicitly write "empty"]
- Ordinary filters/relationship settings that remain active:
  [list them]
- Must include: [specific records/cases]
- Must exclude: [specific records/cases]
- Required order: [specific tie and boundary cases]

Intake gate — complete this before conversion:
1. Treat CMS, exact surface, target model, Legacy Custom Where, and
   Legacy Custom Order as required. A blank, placeholder, broad label,
   or uncertain value is missing or ambiguous. Do not infer it.
2. Treat Legacy Custom Where or Legacy Custom Order as empty only when
   the user explicitly confirms that specific value is empty. An
   omitted or blank value is not confirmation.
3. If any required item is missing or ambiguous, output no JSON and do
   not perform a partial conversion. Ask only for the missing or
   ambiguous items, explain where to find each one, and wait for the
   user's reply:
   - CMS: check the administration dashboard or site information for
     Joomla or WordPress.
   - Surface: copy the exact modern page/component or Site Widget type
     from its editor; distinguish Listings, Comments, Media, Calendar,
     Custom List, Related Listings, and Related Comments.
   - Target model: use the model shown by the supported-surface matrix
     or the target editor; Calendar uses Listing, and Related Comments
     uses Comment.
   - Legacy Custom Where / Order: copy each exact value from the old
     module, widget, shortcode/controller, or Related Reviews
     configuration; if one has no value, explicitly confirm that it is
     empty.
4. Once the exact surface is known, derive the allowed context tokens
   from the documented context-token matrix. Never ask the user to
   provide or guess the token list. If the stated surface does not map
   unambiguously to one matrix row, ask for the exact surface and wait.

Conversion rules — use only after the intake gate passes:
1. Preserve intent; do not preserve platform table or column syntax.
2. Use the documented cross-CMS model fields. Do not output raw post_* or
   CMS table columns.
3. Do not output raw SQL, table names, joins, subqueries, comments, or
   unsupported functions.
4. Do not assume a token merely because the model schema knows its
   name; use only tokens supplied by the stated surface according to
   the documented matrix.
5. If any intent is unsupported, do not invent an equivalent. Mark the
   migration incomplete and explain the exact unsupported requirement.
6. Respect caller-owned eligibility predicates; JSON WHERE is
   AND-composed with them and cannot widen the eligible row set. Do not
   claim that ordering or limits preserve which eligible rows appear.

Return exactly these sections:
A. JSON Custom Query, or "INCOMPLETE — DO NOT USE" when a faithful
   conversion is impossible
B. Field/relation mapping from each legacy expression
C. Assumptions
D. Unsupported or unresolved items
E. Verification cases covering included, excluded, order-tie,
   anonymous-viewer, empty-result, and surface-token behavior

#Review the output

Reject an AI result if it includes a key not documented here, raw table/column names, CURRENT_DATE, unsupported interval units, a subquery, or a token absent from the surface matrix. “Incomplete” is the correct output when the old SQL cannot be represented faithfully.

#Saving and Checking Your Query

The JSON Custom Query setting is available in Listings, Comments, Media, and Calendar Site Widgets, as well as the supported Custom List and related-content settings described above. In a Site Widget editor, select Read the documentation below the setting to return to this guide.

Before saving, remember:

• A JSON order replaces the widget's regular Order By setting. In a Calendar widget, it replaces Listings Order. If the JSON has no order, the regular setting remains active.
• Site Widgets can use information about the person viewing the site and the current date. They cannot use information from the listing page where the widget happens to appear.
• Comments and Media widgets can still use their regular settings to limit results to specific listings.
• JReviews does not attempt to convert an imported Legacy Custom Where value. Use the migration workflow to convert and verify it.

#What happens when you click Save

JReviews checks the JSON before saving it. The check catches malformed JSON, misspelled or unavailable fields, unsupported operations, context values that are not available in that location, and queries that are too complex.

If there is a problem, the query is not saved. The error shows what went wrong and where it occurred. For example, $.where[0].token means the problem is in the token entry of the first condition inside where.

If the query passes, save the widget or page and preview the results. Confirm that known records are included or excluded as expected and that the order is correct. Test while signed in and signed out when the query depends on the current user.

Related Listings and Related Comments perform the same checks when you save their settings.

#Common validation messages

The technical JQ_... code shown with an error is useful when contacting support, but you normally only need the message and location to correct the query.

#Troubleshooting

#The form shows an error location and code

Use the location to find the affected part of the JSON. Keep the code if you need to contact support. Correct unknown or repeated entries instead of removing the widget's regular access and filtering settings, and use the field names in this guide rather than database column names.

#A valid query returns no results

Check the regular widget or page filters first, then confirm the selected model, documented field or relationship, values, and current viewer or page context. For token queries, test both an authenticated viewer and an anonymous viewer. An unavailable token intentionally produces no matches.

#A previously saved query stops returning results

JReviews checks the query every time it runs. A software update, configuration change, missing listing owner, or signed-out visitor can make a field or context value unavailable. Open and save the setting again to see the validation message, then check the JReviews logs for more detail.

#Ordering is unexpected

If the JSON has no order, the widget or page uses its regular ordering setting. If the JSON includes order, it replaces that setting. When several records have the same value, add the model's ID field as the final ordering rule so the result is predictable.

#Verification Checklist

• [ ] Identify the exact widget, Custom List, Related Listings, or Related Comments setting you are changing.
• [ ] Use only fields and relationships listed for its Listing, Comment, or Media model.
• [ ] If the query uses a context token, confirm that the supported-surfaces table lists it for this exact location.
• [ ] Keep the widget or page's regular access, publication, date, and content filters configured as intended.
• [ ] Confirm whether JSON ordering should replace the regular ordering setting.
• [ ] Preview a record that should appear, one that should not appear, an empty-result case, and records with matching order values.
• [ ] Test while signed in and signed out when the query uses information about the current user.
• [ ] Test Joomla and WordPress separately if you operate both.
• [ ] If a legacy requirement cannot be represented by the documented options, keep it on the legacy path or redesign it. Do not substitute a query that only approximates the requirement.

#Related Documentation

• Custom Lists
• Site Widgets
• Related Listings
• Early Access Features
• Release Notes