1

Do SQL Server or MYSQL contain JSON supporting functions? If so, then please tell me the name of the functions or where else I can find it?

I am new in this stuff and I want to implement JSON with the databases mentioned above.

3

SQL Server 2016 will support JSON strings.

Currently with the CTP2 version you can only export a table to JSON string. But with the release of CTP3 version you can also read and store JSON string in SQL Server tables.

You can check all about how JSON will work with SQL Server on my blog. SQL Server will store JSON in NVARCHAR datatype columns only, unlike XML datatype for XML data.

Edit: With CTP 3.x you can create a Table with NVARCHAR(max) data type column to store JSON text, but make sure to add a CHECK constraint to validate the JSON, like:

CREATE TABLE CustomerOrder (
    BusinessEntityID INT, 
    FirstName NVARCHAR(50), 
    MiddleName NVARCHAR(50), 
    LastName NVARCHAR(50), 
    EmailPromotion INT,

    OrderDetailsJSON NVARCHAR(MAX) -- normal column with NVARCHAR datatype
        CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) -- CHECK Constraint to validate JSON string
)

The official SQL Server team blog also has a series of articles on this topic.

  • One problem I see with the SQL Server 2016 approach is that an NVARCHAR(max) can contain JSON and non-json data, and all it takes is one invalid JSON value in your entire table, to break your whole database. There does not appear to be a way to enforce JSON validation on insert. – Warren P Feb 9 '16 at 19:07
  • Hi Warren, while creating a JSON table with NVARCHAR(max) column you can add a CHECK constraint that would validate the JSON text, like this: OrderDetailsJSON NVARCHAR(MAX) CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) – Manoj Pandey Feb 10 '16 at 4:13
2

Not in MySQL old releases, that is, MySQL version < 5.7.
Now (from the docs):

As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.

  • Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

Read the docs: JSON Data Type

1

For MySQL Oracle recently posted JSON UDFs on http://labs.mysql.com .

You can play with them, but they're not for production.

1

MariaDB (a variant of MySQL) has implemented a new feature called Dynamic Columns. Explanation from that page:

Dynamic columns allows one to store different sets of columns for each row in a table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.

Dynamic columns should be used when it is not possible to use regular columns.

A typical use case is when one needs to store items that may have many different attributes (like size, color, weight, etc), and the set of possible attributes is very large and/or unknown in advance. In that case, attributes can be put into dynamic columns.

There are certain limitations though like allowing only numbers as columns names in the production release versions (5.3 and 5.5). There are several functions to manipulate date from such columns but I can't see one for converting JSON formatted data into rows.

I see it also has a COLUMN_JSON() function that returns a JSON representation of data stored in dynamic columns - but this function is introduced at MariaDB 10.0.1 which is not yet GA (only as Alpha release).

1

It does now. With the new functions and JSON data type introduced in 5.7 release( https://dev.mysql.com/doc/refman/5.7/en/json.html).

0

You'll need to make parse functions on your own if you want SQL Server to handle JSON within SQL Server. There's nothing native built in.

I doubt you'd get much performance out of it to be frank and I suspect you'd be much better handling it outside.

0

Not very long ago MySQL started the support of JSON datatype. Here..>> https://dev.mysql.com/doc/refman/5.7/en/json.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.