skills.rest
mariadb-corporation
mariadb-corporationOFFICIAL
Explore all skills by this author →
WebsiteGitHub

mariadb-json-functions

Catalogs MariaDB JSON functions with signatures and semantics for SQL construction, querying, and modification.

28|115|Updated Jan 28, 2025
Search Tags:#SQL#database#json#mariadb#JSONPath#json functions#json_table
One-click install
npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-json-functions-mariadb-corporation
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-json-functions
Source: https://github.com/mariadb-corporation/mariadb-docs/tree/main/agent-skills/granular/functions/mariadb-json-functions
Command: npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-json-functions-mariadb-corporation

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing correct JSON-handling SQL in MariaDB requires knowing which of the 40+ JSON functions exists, its exact signature, and version availability — and avoiding common traps like confusing JSON_VALUE with JSON_QUERY or misplacing JSON_TABLE in the SELECT list. ## Core Features & Use Cases - Complete Function Catalog: Covers constructors (JSON_ARRAY, JSON_OBJECT), aggregates (JSON_ARRAYAGG, JSON_OBJECTAGG), extraction (JSON_EXTRACT, JSON_VALUE, JSON_QUERY, JSON_TABLE), modification (JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE), merge variants, validation (JSON_VALID, JSON_SCHEMA_VALID), and formatting functions with signatures and one-line semantics. - Common Pitfall Corrections: Documents what LLMs often get wrong, such as MariaDB's JSON type being an alias for LONGTEXT with an automatic CHECK constraint, and the difference between -> and ->> operators. - Version Annotations: Flags functions by minimum MariaDB version (e.g., JSON_SCHEMA_VALID since 11.1, JSON_TABLE since 10.6), defaulting to 11.8 LTS context. - Use Case: When writing a query that extracts a scalar from a JSON column, the Skill directs you to use JSON_VALUE or the ->> operator instead of JSON_EXTRACT, avoiding quoted-string bugs. ## Quick Start Ask the AI to write a MariaDB query that extracts values from a JSON column or builds JSON output, and it will apply the correct functions and operators from this catalog.

Frequently Asked Questions about mariadb-json-functions

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I extract a scalar value from JSON in MariaDB?▼

Use JSON_VALUE(json_doc, path) to return a scalar, or the ->> operator which combines JSON_EXTRACT with JSON_UNQUOTE. JSON_EXTRACT alone returns the JSON representation including string quotes, which is a frequent source of bugs.

What is the difference between JSON_VALUE and JSON_QUERY in MariaDB?▼

JSON_VALUE returns a scalar value or NULL, while JSON_QUERY returns a JSON object or array or NULL. They are not interchangeable, and using the wrong one is a common bug when querying JSON documents.

Does MariaDB have a native binary JSON column type?▼

No. MariaDB's JSON type is an alias for LONGTEXT with an automatic CHECK (JSON_VALID(column)) constraint. Operations parse the text at query time, so adding your own JSON_VALID check is redundant.

How do I use JSON_TABLE in a MariaDB query?▼

JSON_TABLE is a table function that goes in the FROM clause or as a derived table, not in the SELECT list. It projects a JSON document into relational form and is available since MariaDB 10.6.

Which MariaDB version supports JSON_SCHEMA_VALID?▼

JSON_SCHEMA_VALID is available since MariaDB 11.1. It returns only 0 or 1 with no diagnostic about what failed validation, so use a dedicated JSON-schema tool if you need detailed error reporting.

Why does JSON_EXTRACT return multiple values as an array?▼

When a JSONPath matches multiple values, MariaDB autowraps the result as a JSON array. For example, JSON_EXTRACT('[1,2,3]', '$[*]') returns [1, 2, 3]. Use specific paths like $[0] for single-value extraction.

Related Skills

Explore More Software Engineering Skills→
Impertio-StudioImpertio-Studio
2

mariadb-syntax-check-constraints

Add CHECK constraints to MariaDB columns and tables with JSON_VALID validation.

Official
Intermediate
lostitonce420-beeplostitonce420-beep

file-converter

Convert data and documents between CSV, JSON, YAML, XML, Markdown, and image formats.

Community
Intermediate
Impertio-StudioImpertio-Studio
2

mariadb-syntax-json

Validate JSON columns, extract data with JSON_* functions, and create functional indexes in MariaDB.

Official
Advanced
mdnaimul22mdnaimul22
2

database-designer

Design relational database schemas and generate index recommendations with migration SQL.

Community
Advanced