CODEWITHSUNDEEP
mysqljsonlaraveljsonschema
santiago arizti
santiago arizti

Reputation: 4737

MySql JSON Schema

I have a table in MySql somewhat like this:

create table json_docs (
  id int auto_increment primary_key,
  doc json not null
)

I also have a json schema that this doc column obeys.

{
  "$id": "https://my.org/namespace/doc.schema.json",
  "type": "object",
  "properties": {...}
}

Is there a way to enforce that schema in MySql during an insert or update?

insert into json_docs (doc) values ('{...}')

I am also using PHP Laravel. If there is a way to do this in the Model layer of Laravel I would also be interested.

Thanks in advance for your help!

Upvotes: 3

Views: 5203

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

MySQL does not have any feature built-in to validate JSON documents against a schema.

Update: This feature now exists in 8.0.17, but prior to that version, the following answer still applies:

You would have to validate your document in your app, independently from inserting it into a database.

See https://github.com/justinrainbow/json-schema for an open-source JSON validator in PHP.

Upvotes: 1

Morgan Christiansson
Morgan Christiansson

Reputation: 29450

MySQL does have json validation support.

https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html

Upvotes: 2

Related Questions