JsonNativeField: key-value table + native JSON = fully searchable

[BitPoet]

Another proof-of-concept module:

JsonNativeField

Leverages MySQL >= 5.7.8's native JSON column type for key-value storage.

This gives us the opportunity to store arbitrary (textual) fields in the database and allows us to search for them with plain subfield selector syntax, including wildcard operators. Possible applications are storing submitted form data or adding user-defined properties to their profile.

Currently, the module is still really, really alpha, but I wanted to get input early on.  Let me know what you think.

Download from GitHub.

Here are a few screenshots for anybody interested but unable to try for themselves:

1. First page with json field and custom subfields:

 

2. Second page with json field and custom subfields:

 

3. Searching through all subfields in the json field:

 

4. Searching only in a specific subfield:

 

The interface is really rather crude still. The "delete" buttons only work after saving (have to attach listeners to the newly created entries yet), and I've got to straighten out supported operators for Lister to pick up. I'll see if I find some time tomorrow to work on these issues and brush up the visual side a bit.

Edited September 28, 2016 by BitPoet
Added screenshots

[tpr]

Sounds great, unfortunately the MySQL requirements doesn't allow me to try. Any chance to add a screenshot to see something "tangible"?

[BitPoet]

25 minutes ago, tpr said:

Sounds great, unfortunately the MySQL requirements doesn't allow me to try. Any chance to add a screenshot to see something "tangible"?

Done.

[tpr]

Thanks, looks even better I often have a need for a WordPress "custom fields"-like field in PW and this module has this.

[Macrura]

this could be a good field to hold site settings when your users need to be able to keep defining/adding settings.

[BitPoet]

Just a quick status update: I've started implementing different field types (text, integer, float, date, date+time) with native inputfields. Types can be switched back and forth, and I'll make it hookable so custom inputfields can be added as well. Will be a while though, since it looks like I'll be busy with a lot of other stuff until the end of next week. Here's a teaser though...

[BitPoet]

Unexpectedly found a half day to spare, so I've pushed 0.0.3 to GitHub.

New features:

• Field settings for custom date, date+time and text fields can now be made on the inputfield's input tab, e.g. date and time formats, placeholder text or text validatoin patterns (numeric field settings are on the todo list)
• Names for custom fields are limited to start and end with ASCII letters and only have alphanumeric ASCII characters and hyphens in between
• Custom field name of "data" is silently stripped to avoid bollixing up all entries
• The custom fields table in the backend is now sorted by name (new entries simply get appended)

The module is still alpha. Things are getting smoother, but there are still a few items on the todo list.

[thetuningspoon]

@BitPoet

Wow, this is perfect timing, as I am just working on the requirements for a project that requires storing a lot of JSON.

Does this enable storage and retrieval of nested JSON content that is multiple layers deep? E.g. $pages->find('jsonField.subField.field=foo') ?

Obviously this would present some challenges for displaying the data on the back end using your current tabular format. Maybe a nested list (like ProcessWire's page tree) would be a more scalable approach?

[BitPoet]

I admit I haven't thought too much about nesting, and the wiring around the field's json data would obviously need to be completely different. InnoDB does support searching in nested structures, but PW already treats subfields on the left side of a selector in its own way and throws away anything starting at the second full stop. A different syntax would be necessary, e.g.

$pages->find('jsonField=/subField/field:foo') 

There might be issues where search expressions need some kind of escaping (e.g. searching for literal text "/subfield"), but this would allow for wildcard queries.

$pages->find('jsonField=/subField/*/name:foo') 

Or even searching arrays (any element or by index):

$pages->find('jsonField=/subField/fields#*/name:foo');

$pages->find('jsonField=/subField/field#12/name:foo');

Here's a tiny adoption of my module to allow this search syntax. There's no back and forth conversion though, just a textarea containing the JSON.

<?php

class FieldtypeJsonDocument extends FieldtypeTextarea implements Module {
	public static function getModuleInfo() {
		return array(
			"title"				=>	"Fieldtype Json Document",
			"summary"			=>	"Fieldtype utilizing native MySQL support for searching JSON documents.",
			"version"			=>	"0.0.3",
		);
	}
	
	public function getMatchQuery($query, $table, $subfield, $operator, $value) {
		$database = $this->wire("database");

		list($path, $value) = explode(':', $value, 2);
		if(empty($value) && !empty($path)) {
			$value = $path;
			$path = "";
		}
		$path = '$' . ((empty($subfield) || $subfield == "data") ? "" : ".$subfield") . (empty($path) ? ".*" : str_replace('/', '.', preg_replace('~/?#(\d+|\*)~', "[$1]", $path)));

		$table = $database->escapeTable($table);
		$value = $database->escapeStr($value);
		if($operator == "=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '$value', NULL, '$path') IS NOT NULL");			
		} else if($operator == "*=" || $operator == "%=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '%$value%', NULL, '$path') IS NOT NULL");			
		} else if($operator == "^=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '$value%', NULL, '$path') IS NOT NULL");			
		} else if($operator == "$=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '%$value', NULL, '$path') IS NOT NULL");			
		}

		$this->log->message($query->getQuery());

		return $query; 
	}

	
	public function getDatabaseSchema(Field $field) {
		$engine = $this->wire('config')->dbEngine; 
		$charset = $this->wire('config')->dbCharset;
		$schema = array(
			'pages_id' => 'int UNSIGNED NOT NULL', 
			'data' => "JSON", // each Fieldtype should override this in particular
			'keys' => array(
				'primary' => 'PRIMARY KEY (`pages_id`)',
			),
			// additional data 
			'xtra' => array(
				// any optional statements that should follow after the closing paren (i.e. engine, default charset, etc)
				'append' => "ENGINE=$engine DEFAULT CHARSET=$charset", 
				
				// true (default) if this schema provides all storage for this fieldtype.
				// false if other storage is involved with this fieldtype, beyond this schema (like repeaters, PageTable, etc.)
				'all' => true, 
			)
		); 
		return $schema; 
	}

    public function getInputfield(Page $page, Field $field) {
        $inputField = $this->modules->get('InputfieldTextarea');
        return $inputField;
    }
	
	public function install() {
		if($this->config->dbEngine != "InnoDB") {
			throw new WireException($this->_("InnoDB database engine needs to be used for native JSON support"));
		}
		
		$dbver = $this->database->getAttribute(PDO::ATTR_SERVER_VERSION);
		if(version_compare($dbver, '5.7.8', '<')) {
			throw new WireException(sprintf($this->_("MySQL Server version needs to be at least 5.7.8 for fully working JSON support, installed version is %s"), $dbver));
		}
	}
}

There would probably be a bit of recursive shuffling necessary in sleep-/wakeup-/sanitizeValue to build nested structures based on WireData/WireArray so accessing subfields and filtering PageArrays is possible, but that should be doable too. How difficult the backend input + display parts get depends solely on your exact requirements.

[BitPoet]

Status update: JsonNativeField version 0.0.4 now has configuration options for all field types in the field's input settings. Coming up next is some more data validation (i.e. "don't shoot your foot" checking) both in the UI when switching input type and in the module code when saving to and loading from the db. This will be the major step towards moving the module from alpha to beta stage.

[WinnieB]

Curious, any plans to develop this further?

[BitPoet]

It's a bit on the back burner right now. I was hoping for some more elaborate possibilities with MySQL 8, but the changes to JSON support there were on the homeopathic end rather than the dynamic typing support I had hoped for. I plan to brush up the UI and client side validation a bit once PW 3.1 gets out, though, and test things out in depth with UIkit admin theme.

[rick]

I've been searching for a viable means to store json without having to use a document db as well. Is this still on the back burner? If not, any suggestions on efficiently searching large datasets?
Thanks!

[wbmnfktr]

Maybe totally OT but I had to store some really large JSON objects and therefore stored them straight in pages. Maybe not ideal but even after 12,000 entries/pages the system responds super fast.

If I remember correctly I used a textarea field and never had any issues.

Not perfect but... well... it works.

[rick]

Yes, that is what I am currently doing. It saves the data fine and I can read the data fine. Searching values from keys, not so much. lol

[wbmnfktr]

My search was based on "client entries" so they can browse through all requests and saved entries they ever made.

Maybe I find that code again or can gain access to the system. If so... I will let you know.

[rick]

I'm having to read the whole dataset into an array in order to process entries. Some pages have 5K+ entries, others are more manageable. It's a global Company <--> Contractor relationship.

[wbmnfktr]

Ok... you stepped up my game. No doubt. 
Still... I'll try to get into my old code.

[Ivan Gretsky]

Good day, @BitPoet!

I am looking at this field to implement custom properties for pages without a predefined schema, but so they can still be searchable by them. I can't think of a better solution, than this field. It really looks awesome!

I need to fill those properties via API during import. I haven's spotted field API docs neither here nor on github. Did I miss them? If there aren't any, could you please share an example of reading from/writing to this field?

[BitPoet]

On 8/6/2021 at 3:38 PM, Ivan Gretsky said:

Good day, @BitPoet!

I am looking at this field to implement custom properties for pages without a predefined schema, but so they can still be searchable by them. I can't think of a better solution, than this field. It really looks awesome!

I need to fill those properties via API during import. I haven's spotted field API docs neither here nor on github. Did I miss them? If there aren't any, could you please share an example of reading from/writing to this field?

Hi Ivan,

I never really looked at API usage up to now (and once I did, I noticed that the module really should be namespaced).

Here's a short example for filling values in code:

<?php namespace ProcessWire;

/*
	Fill the JsonNativeField field "nativefield" in Page $p through API
*/

$fdata = new \JsonData();		// This is the class for a JsonNativeFieldValue
$fdata->set('first-row', new \JsonValue('text', 'Text in first row'));
$fdata->set('second-row', new \JsonValue('integer', 123));
$fdata->set('third-row', new \JsonValue('float', 3.1415927));
$fdata->set('fourth-row', new \JsonValue('date', time());
$fdata->set('fifth-row', new \JsonValue('datetime', time() + 3600));

$p->of(false);
$p->nativefield = $fdata;
$p->save();
            
/* Change value for "fifth-row" in our field */
$p>-nativeField->set('fifth-row', new \JsonValue('datetime', time() - 3600));
$p->save();

 

 

[Ivan Gretsky]

Thank you, @BitPoet!

While you're at it, maybe you'll have time to look at this issue? Solving it could make using this field possible for existing installations and therefore more common. I think this module deserves more attention and usage.

[BitPoet]

19 hours ago, Ivan Gretsky said:

While you're at it, maybe you'll have time to look at this issue? Solving it could make using this field possible for existing installations and therefore more common. I think this module deserves more attention and usage.

I've changed that so it only checks for minimum MySQL Version. Still, the module is very much alpha code and work-in-progress. I haven't gotten around to apply and test the field type configuration options, and there are quite a few rough spots in the existing code.

FYI, I decided to rename my helper classes as I realized that JsonData and JsonValue may make nice names for core classes. To avoid clashing and hogging names I've prepended "JN", so it's JNJsonData and JNJsonValue now.

The new release is still in testing stage and can be found here.

Edited August 20, 2021 by BitPoet
Added link to dev tree in github repo

[Ivan Gretsky]

1. Thanks, @BitPoet! I did install the dev version in a MyIsam db. It works!

2. This doesn't seem to work. I get an error Call to a member function set() on string. Am I doing something wrong?

/* Change value for "fifth-row" in our field */
$p>-nativeField->set('fifth-row', new \JsonValue('datetime', time() - 3600));
$p->save();

3. You gave example of how to set new values. Can you help with getting the value of a specified key and outputting it.

4. ...and adding new key/value fields to an existing set.

5. What are field type configuration options? Are you talking about setting a new custom type (for example, not just text, but email / phone) with its own customizable pattern checks and so on? That was exactly what I was about to propose. The other cool thing would be to use custom inputfields for types. Like Page Reference to get a comma separated pages list and store it as a string.

6. Another idea is the ability to limit the names and/or name/type combinations to a predefined set, so the user can only choose from the list, but not create new key/value pairs.

I do understand, that this module is probably not your top priority, as it was here unchanged for a while. But just can't resist writing here all my wild wishes)

[horst]

4 hours ago, Ivan Gretsky said:

$p>-nativeField->set('fifth-row', new \JsonValue('datetime', time() - 3600));

 

7 hours ago, BitPoet said:

FYI, I decided to rename my helper classes

 

7 hours ago, BitPoet said:

I've prepended "JN", so it's JNJsonData and JNJsonValue now.

 

Try:

$p>-nativeField->set('fifth-row', new \JNJsonValue('datetime', ti

 

[Ivan Gretsky]

I did try it with the updated class names. Sorry for not mentioning that. But that did not help ?