Tag: JSON

Feed: Planet MySQL; Author: Michael McLaughlin; +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | struct | +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | {"contact": [{"last_name": "Winn", "first_name": "Randi"}, {"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} | | 2 | {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"}, {"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} | | 3 | {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"}, {"last_name": "Sweeney", "first_name": "Matthew"}, {"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} | | 4 | {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} | | 5 | {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} | | 6 | {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} | | 7 | {"contact": [{"last_name": ... Read More

Feed: Planet MySQL; Author: Michael McLaughlin; Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post. Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create ... Read More

Feed: Recent Announcements. Amazon ElastiCache for Redis and Amazon MemoryDB for Redis now support natively storing and accessing data in the JavaScript Object Notation (JSON) format. With this launch, application developers can effortlessly store, fetch, and update their JSON data inside Redis without needing to manage custom code for serialization and deserialization. Using ElastiCache and MemoryDB, you can now efficiently retrieve and update specific portions of a JSON document without needing to manipulate the entire object, which can help improve performance and help reduce cost. You can also search your JSON document contents using the JSONPath query syntax ... Read More

Feed: Recent Announcements. Amazon Relational Database Service (Amazon RDS) Data API can now return results in a new simplified JSON format that makes it easier to convert JSON string to an object in your application. Previously, Amazon RDS Data API returned a JSON string as an array of data type and value pairs. This required developers to write custom code to parse the response and extract the values in order to manually translate the JSON string into an object. Instead, the new format returns an array of column names and values, which makes it easier for common JSON parsing libraries ... Read More

Feed: Planet PostgreSQL. On 8th of April 2022, Andrew Dunstan committed patch: Documentation for SQL/JSON features   This documents the features added in commits f79b803dcc, f4fb45d15c, 33a377608f, 1a36bc9dba, 606948b058, 49082c2cc3, 4e34747c88, and fadb48b00e.   I have cleaned up the aggregate section of the submitted docs, but there is still a deal of copy editing required. However, I thought it best to have some documentation sooner rather than later so testers can have a better idea what they are playing with.   Nikita Glukhov   Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik ... Read More

Feed: Planet MySQL; Author: Frederic Descamps; Let’s start with some info about how MySQL Document Store handles JSON documents. Document Store and CRUD We know that MySQL 8.0 Document Store handles JSON documents with CRUD operations. We can add, delete and modify those documents very easily: JS > db.mycollection.find() { "_id": "0000624d3e890000000000000001", "name": "my_iot1", "type": "sensor", "capabilities": "{'temperature':'true','humidity':'true'}" } { "_id": "0000624d3e890000000000000002", "name": "my_iot2", "type": "sensor", "capabilities": "{'temperature':'true'}" } 2 documents in set (0.0007 sec) To modify a document, the modify method can be used in different ways: As illustrate above, we have: set()unset()patch()and arrays related methods Let’s have a ... Read More

Feed: Planet PostgreSQL. On 29th of March 2022, Andrew Dunstan committed patch: SQL/JSON query functions   This introduces the SQL/JSON functions for querying JSON data using jsonpath expressions. The functions are:   JSON_EXISTS() JSON_QUERY() JSON_VALUE()   All of these functions only operate on jsonb. The workaround for now is to cast the argument to jsonb.   JSON_EXISTS() tests if the jsonpath expression applied to the jsonb value yields any values. JSON_VALUE() must return a single value, and an error occurs if it tries to return multiple values. JSON_QUERY() must return a json object or array, and there are various WRAPPER ... Read More

Feed: Planet PostgreSQL. On 28th of March 2022, Andrew Dunstan New Blog Post Title: Waiting for PostgreSQL 15 – IS JSON predicate On 28th of March 2022, Andrew Dunstan committed patch: IS JSON predicate   This patch intrdocuces the SQL standard IS JSON predicate. It operates on text and bytea values representing JSON as well as on the json and jsonb types. Each test has an IS and IS NOT variant. The tests are:   IS JSON [VALUE] IS JSON ARRAY IS JSON OBJECT IS JSON SCALAR IS JSON WITH | WITHOUT UNIQUE KEYS   These are mostly self-explanatory, but ... Read More

Feed: Planet PostgreSQL. On 27th of March 2022, Andrew Dunstan committed patch: SQL/JSON constructors   This patch introduces the SQL/JSON standard constructors for JSON:   JSON() JSON_ARRAY() JSON_ARRAYAGG() JSON_OBJECT() JSON_OBJECTAGG()   For the most part these functions provide facilities that mimic existing json/jsonb functions. However, they also offer some useful additional functionality. In addition to text input, the JSON() function accepts bytea input, which it will decode and constuct a json value from. The other functions provide useful options for handling duplicate keys and null values.   This series of patches will be followed by a consolidated documentation patch.   ... Read More

Feed: Planet PostgreSQL. Slide from my talk Understanding Jsonb performance at PGCONF.NYC, Dec 2, 2021, with some corrections.I learned about new version of SQL standard (Fall of 2016, SQL-2016) with SQL/JSON Specification and instantly bought electronic copy of this standard. I spent several days reading it and decided to start project "SQL/JSON in PostgreSQL". It was obvious that our #JSONB group should work on this and I decided this project should be Postgres Professional contribution to #PostgreSQL community. First patches were ready in 2017, jsonpath was committed in 2019 for PG13. Now, after several years other patches are on the ... Read More