Jump to content

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


BitPoet

Recommended Posts

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:

jsonfield1-page1.png

 

2. Second page with json field and custom subfields:

jsonfield2-page2.png

 

3. Searching through all subfields in the json field:

jsonfield3-search-all-subfields.png

 

4. Searching only in a specific subfield:

jsonfield4-search-specific-subfield.png

 

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 by BitPoet
Added screenshots
  • Like 19
Link to comment
Share on other sites

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...

jsonfield5-types.png

  • Like 8
Link to comment
Share on other sites

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. :)

  • Like 10
Link to comment
Share on other sites

@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?

Link to comment
Share on other sites

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.

  • Like 4
Link to comment
Share on other sites

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.

  • Like 6
Link to comment
Share on other sites

  • 1 year later...

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.

  • Like 6
Link to comment
Share on other sites

  • 2 years later...

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!

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...

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?

Link to comment
Share on other sites

  • 2 weeks later...
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();

 

 

  • Like 3
Link to comment
Share on other sites

Posted (edited)
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 by BitPoet
Added link to dev tree in github repo
  • Like 3
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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


 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Robin S
      This module lets you add some custom menu items to the main admin menu, and you can set the dropdown links dynamically in a hook if needed.
      Sidenote: the module config uses some repeatable/sortable rows for the child link settings, similar to the ProFields Table interface. The data gets saved as JSON in a hidden textarea field. Might be interesting to other module developers?
      Custom Admin Menus
      Adds up to three custom menu items with optional dropdowns to the main admin menu.
      The menu items can link to admin pages, front-end pages, or pages on external websites.
      The links can be set to open in a new browser tab, and child links in the dropdown can be given an icon.
      Requires ProcessWire v3.0.178 or newer.
      Screenshots
      Example of menu items

      Module config for the menus

      Link list shown when parent menu item is not given a URL

      Advanced
      Setting child menu items dynamically
      If needed you can set the child menu items dynamically using a hook.
      Example:
      $wire->addHookAfter('CustomAdminMenus::getMenuChildren', function(HookEvent $event) { // The menu number is the first argument $menu_number = $event->arguments(0); if($menu_number === 1) { $colours = $event->wire()->pages->findRaw('template=colour', ['title', 'url', 'page_icon']); $children = []; foreach($colours as $colour) { // Each child item should be an array with the following keys $children[] = [ 'icon' => $colour['page_icon'], 'label' => $colour['title'], 'url' => $colour['url'], 'newtab' => false, ]; } $event->return = $children; } }); Create multiple levels of flyout menus
      It's also possible to create multiple levels of flyout submenus using a hook.

      For each level a submenu can be defined in a "children" item. Example:
      $wire->addHookAfter('CustomAdminMenus::getMenuChildren', function(HookEvent $event) { // The menu number is the first argument $menu_number = $event->arguments(0); if($menu_number === 1) { $children = [ [ 'icon' => 'adjust', 'label' => 'One', 'url' => '/one/', 'newtab' => false, ], [ 'icon' => 'anchor', 'label' => 'Two', 'url' => '/two/', 'newtab' => false, 'children' => [ [ 'icon' => 'child', 'label' => 'Red', 'url' => '/red/', 'newtab' => false, ], [ 'icon' => 'bullhorn', 'label' => 'Green', 'url' => '/green/', 'newtab' => false, 'children' => [ [ 'icon' => 'wifi', 'label' => 'Small', 'url' => '/small/', 'newtab' => true, ], [ 'icon' => 'codepen', 'label' => 'Medium', 'url' => '/medium/', 'newtab' => false, ], [ 'icon' => 'cogs', 'label' => 'Large', 'url' => '/large/', 'newtab' => false, ], ] ], [ 'icon' => 'futbol-o', 'label' => 'Blue', 'url' => '/blue/', 'newtab' => true, ], ] ], [ 'icon' => 'hand-o-left', 'label' => 'Three', 'url' => '/three/', 'newtab' => false, ], ]; $event->return = $children; } }); Showing/hiding menus according to user role
      You can determine which menu items can be seen by a role by checking the user's role in the hook.
      For example, if a user has or lacks a role you could include different child menu items in the hook return value. Or if you want to conditionally hide a custom menu altogether you can set the return value to false. Example:
      $wire->addHookAfter('CustomAdminMenus::getMenuChildren', function(HookEvent $event) { // The menu number is the first argument $menu_number = $event->arguments(0); $user = $event->wire()->user; // For custom menu number 1... if($menu_number === 1) { // ...if user does not have some particular role... if(!$user->hasRole('foo')) { // ...do not show the menu $event->return = false; } } });  
      https://github.com/Toutouwai/CustomAdminMenus
      https://processwire.com/modules/custom-admin-menus/
    • By tcnet
      This module for ProcessWire sends a notification email for each failed login attempt. Similar modules exists already in the module directory of ProcessWire. However, this module is designed to notify, even if specified user doesn't exist.
      Settings
      The settings for this module are located in the menu Modules=>Configure=>LoginFailNotifier.
      Notification email
      Specifies the email address to which the notification emails should be sent.
        Email subject
      Specifies the subject line for the notification email.
        Post variables
      Specifies the $_POST variables to be included in the notification email. Each variable must be separated by a comma. For example: login_name,login_pass
        Server variables
      Specifies the $_SERVER variables to be included in the notification email. Each variable must be separated by a comma. For example: REMOTE_ADDR,HTTP_USER_AGENT
      Link to ProcessWire module directory:
      https://processwire.com/modules/login-fail-notifier/
      Link to github.com:
      https://github.com/techcnet/LoginFailNotifier
    • By Fokke
      ProcessWire 3.x markup module for rendering meta tags in HTML document head section. Note that this module is not a full-blown SEO solution, but rather a simple tool for rendering meta tags based on module configuration. Adding custom meta tags is also supported.
      Built-in meta tags
      The following meta tags are supported out-of-the-box:
      Document title consisting of page title and site name Character set Canonical Viewport Description Keywords Hreflang tags Open Graph og:title og:site_name og:type og:url og:description og:image og:image:width og:image:height Twitter meta tags twitter:card twitter:site twitter:creator twitter:title twitter:description twitter:image Facebook meta tags fb:app_id The full documentation with configurable options can be found here: https://github.com/Fokke-/MarkupMetadata
       
      Requirements:
      ProcessWire>=3.0.0 PHP >=7.1 Installation using Composer
      composer require fokke/markup-metadata Manual installation
      Download latest version from https://github.com/Fokke-/MarkupMetadata/archive/master.zip Extract module files to site/modules/MarkupMetadata directory.
    • By m.sieber
      ITRK-Service for ProcessWire
      Module for the automated transfer of imprint, data protection declaration and terms and conditions from IT-Recht Kanzlei to your ProcessWire installation
      What is ITRK Service for ProcessWire?
      ITRK-Service for ProcessWire is a free module for ProcessWire CMS. It provides an interface to the update service of IT-Recht Kanzlei, via which the legal texts of your online presence are automatically updated. In this way, the texts remain legally secure and warning-proof in the long term. Imprint, data protection declaration, revocation and general terms and conditions are currently supported.
      You can find our documentation (in german language) here: https://www.pupit.de/itrk-service-for-processwire/dokumentation/

      Download: https://www.pupit.de/itrk-service-for-processwire/
      Github: https://github.com/pupit-de/pwItrkServiceConnector
    • By LuisM
      Symprowire is a PHP MVC Framework based and built on Symfony using ProcessWire 3.x as DBAL and Service-Provider
      It acts as a Drop-In Replacement Module to handle the Request/Response outside the ProcessWire Admin. Even tough Symfony or any other mature MVC Framework could be intimidating at first, Symprowire tries to abstract Configuration and Symfony Internals away as much as possible to give you a quick start and lift the heavy work for you.
      The main Goal is to give an easy path to follow an MVC Approach during development with ProcessWire and open up the available eco-system.
      You can find the GitHub Repo and more Information here: https://github.com/Luis85/symprowire
      Documentation
      The Symprowire Wiki https://github.com/Luis85/symprowire/wiki How to create a simple Blog with Symprowire https://github.com/Luis85/symprowire/wiki/Symprowire-Blog-Tutorial Last Update
      16.07.2021 // RC 1 v0.6.0 centralized ProcessWire access trough out the Application by wrapping to a Service https://github.com/Luis85/symprowire/releases/tag/v0.6.0-rc-1 Requirements
      PHP ^7.4 Fresh ProcessWire ^3.0.181 with a Blank Profile Composer 2 (v1 should work, not recommended) The usual Symfony Requirements Features
      Twig Dependency Injection Monolog for Symprowire Support for .env YAML Configuration Symfony Console and Console Commands Symfony Webprofiler Full ProcessWire access inside your Controller and Services Webpack Encore support Caveats
      Symfony is no small Framework and will come with a price in terms of Memory Usage and added Overhead. To give you a taste I installed Tracy Debugger alongside to compare ProcessWire profiling with the included Symfony Webprofiler

      So in a fresh install Symprowire would atleast add another 2MB of Memory usage and around 40ms in response time, should be less in production due to the added overhead of the Webprofiler in dev env
       
×
×
  • Create New...