Jump to content
BitPoet

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

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

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites
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. :)

  • Like 2

Share this post


Link to post
Share on other sites

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

  • Like 2

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
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 9

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
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 d'Hinnisdaël
      Happy new year, everybody 🥬
      I've been sitting on this Dashboard module I made for a client and finally came around to cleaning it up and releasing it to the wider public. This is how it looks.
      ProcessWire Dashboard

      If anyone is interested in trying this out, please go ahead! I'd love to get some feedback on it. If this proves useful and survives some real-world testing, I'll add this to the module directory.
      Download
      You can find the latest release on Github.
      Documentation
      Check out the documentation to get started. This is where you'll find information about included panel types and configuration options.
      Custom Panels
      My goal was to make it really simple to create custom panels. The easiest way to do that is to use the panel type template and have it render a file in your templates folder. This might be enough for 80% of all use cases. For anything more complex (FormBuilder submissions? Comments? Live chat?), you can add new panel types by creating modules that extend the DashboardPanel base class. Check out the documentation on custom panels or take a look at the HelloWorld panel to get started. I'm happy to merge any user-created modules into the main repo if they might be useful to more than a few people.
       Disclaimer
      This is a pre-release version. Please treat it as such — don't install it on production sites. Just making sure 🍇
      Roadmap
      These are the things I'm looking to implement myself at some point. The wishlist is a lot longer, but those are the 80/20 items that I probably won't regret spending time on.
      Improve documentation & add examples ⚙️ Panel types Google Analytics ⚙️ Add new page  🔥 Drafts 🔥 At a glance / Page counter 404s  Layout options Render multiple tabs per panel panel groups with heading and spacing between ✅ panel wrappers as grid item (e.g. stacked notices) ✅ Admin themes support AdminThemeReno and AdminThemeDefault ✅ Shortcuts panel add a table layout with icon, title & summary ✅ Chart panel add default styles for common chart types ✅ load chart data from JS file (currently passed as PHP array) Collection panel support image columns ✅ add buttons: view all & add new ✅
    • By Pip
      Hi everyone!
      I'm trying out the Login/Register module for my site. Noted that the module assigns the newly registered user to login-register role. 
      Once you modify the login-register role's permissions, particularly adding page-edit, the new member role will be set to guest. 
      Thing is I'd like to grant my new users the power to create their own pages. Any advice? 
      Thanks. 
    • By Gadgetto
      SnipWire - Snipcart integration for ProcessWire
      Snipcart is a powerful 3rd party, developer-first HTML/JavaScript shopping cart platform. SnipWire is the missing link between Snipcart and the content management framework ProcessWire.
      With SnipWire, you can quickly turn any ProcessWire site into a Snipcart online shop. The SnipWire plugin helps you to get your store up and running in no time. Detailed knowledge of the Snipcart system is not required.
      SnipWire is free and open source licensed under Mozilla Public License 2.0! A lot of work and effort has gone into development. It would be nice if you could donate an amount to support further development:

      Status update links (inside this thread) for SnipWire development
      2020-07-03 -- SnipWire 0.8.7 (beta) released! Fixes some small bugs and adds an indicator for TEST mode 2020-04-06 -- SnipWire 0.8.6 (beta) released! Adds support for Snipcart subscriptions and also fixes some problems 2020-03-21 -- SnipWire 0.8.5 (beta) released! Improves SnipWires webhooks interface and provides some other fixes and additions 2020-03-03 -- SnipWire 0.8.4 (beta) released! Improves compatibility for Windows based Systems. 2020-03-01 -- SnipWire 0.8.3 (beta) released! The installation and uninstallation process has been heavily revised. 2020-02-08 -- SnipWire 0.8.2 (beta) released! Added a feature to change the cart and catalogue currency by GET, POST or SESSION param 2020-02-03 -- SnipWire 0.8.1 (beta) released! All custom classes moved into their own namespaces. 2020-02-01 -- SnipWire is now available via ProcessWire's module directory! 2020-01-30 -- SnipWire 0.8.0 (beta) first public release! (module just submitted to the PW modules directory) 2020-01-28 -- added Custom Order Fields feature (first SnipWire release version is near!) 2020-01-21 -- Snipcart v3 - when will the new cart system be implemented? 2020-01-19 -- integrated taxes provider finished (+ very flexible shipping taxes handling) 2020-01-14 -- new date range picker, discount editor, order notifiactions, order statuses, and more ... 2019-11-15 -- orders filter, order details, download + resend invoices, refunds 2019-10-18 -- list filters, REST API improvements, new docs platform, and more ... 2019-08-08 -- dashboard interface, currency selector, managing Orders, Customers and Products, Added a WireTabs, refinded caching behavior 2019-06-15 -- taxes provider, shop templates update, multiCURL implementation, and more ... 2019-06-02 -- FieldtypeSnipWireTaxSelector 2019-05-25 -- SnipWire will be free and open source Plugin Key Features
      Fast and simple store setup Full integration of the Snipcart dashboard into the ProcessWire backend (no need to leave the ProcessWire admin area) Browse and manage orders, customers, discounts, abandoned carts, and more Multi currency support Custom order and cart fields Process refunds and send customer notifications from within the ProcessWire backend Process Abandoned Carts + sending messages to customers from within the ProcessWire backend Complete Snipcart webhooks integration (all events are hookable via ProcessWire hooks) Integrated taxes provider (which is more flexible then Snipcart own provider) Useful Links
      SnipWire in PW modules directory SnipWire Docs (please note that the documentation is a work in progress) SnipWire @GitHub (feature requests and suggestions for improvement are welcome - I also accept pull requests) Snipcart Website  

       
      ---- INITIAL POST FROM 2019-05-25 ----
       
    • By Sten
      Hello
      Till now I hacked something with the twig template but it works no more with new PW versions so I look forward to create a module. I am working on a site in multiple languages : French, English, Italian, German, Spanish, Portuguese, Hebrew, Russian. The new posts are entered in any language with a field for language. Till now, I got twig files to get the translations with constants defined for each part of the pages.
      So I'd like to create a module to include theses files added according to the url /fr/en/...
      Have you some observations to do before I begin about the direction to take ?
      Thank you
    • By ukyo
      Mystique Module for ProcessWire CMS/CMF
      Github repo : https://github.com/trk/Mystique
      Mystique module allow you to create dynamic fields and store dynamic fields data on database by using a config file.
      Requirements
      ProcessWire 3.0 or newer PHP 7.0 or newer FieldtypeMystique InputfieldMystique Installation
      Install the module from the modules directory:
      Via Composer:
      composer require trk/mystique Via git clone:
      cd your-processwire-project-folder/ cd site/modules/ git clone https://github.com/trk/Mystique.git Module in live reaction with your Mystique config file
      This mean if you remove a field from your config file, field will be removed from edit screen. As you see on youtube video.
      Using Mystique with your module or use different configs path, autoload need to be true for modules
      Default configs path is site/templates/configs/, and your config file name need to start with Mystique. and need to end with .php extension.
      Adding custom path not supporting anymore !
      // Add your custom path inside your module class`init` function, didn't tested outside public function init() { $path = __DIR__ . DIRECTORY_SEPARATOR . 'configs' . DIRECTORY_SEPARATOR; Mystique::add($path); } Mystique module will search site/modules/**/configs/Mystique.*.php and site/templates/Mystique.*.php paths for Mystique config files.
      All config files need to return a PHP ARRAY like examples.
      Usage almost same with ProcessWire Inputfield Api, only difference is set and showIf usage like on example.
      <?php namespace ProcessWire; /** * Resource : testing-mystique */ return [ 'title' => __('Testing Mystique'), 'fields' => [ 'text_field' => [ 'label' => __('You can use short named types'), 'description' => __('In file showIf working like example'), 'notes' => __('Also you can use $input->set() method'), 'type' => 'text', 'showIf' => [ 'another_text' => "=''" ], 'set' => [ 'showCount' => InputfieldText::showCountChars, 'maxlength' => 255 ], 'attr' => [ 'attr-foo' => 'bar', 'attr-bar' => 'foo' ] ], 'another_text' => [ 'label' => __('Another text field (default type is text)') ] ] ]; Example:
      site/templates/configs/Mystique.seo-fields.php <?php namespace ProcessWire; /** * Resource : seo-fields */ return [ 'title' => __('Seo fields'), 'fields' => [ 'window_title' => [ 'label' => __('Window title'), 'type' => Mystique::TEXT, // or InputfieldText 'useLanguages' => true, 'attr' => [ 'placeholder' => __('Enter a window title') ] ], 'navigation_title' => [ 'label' => __('Navigation title'), 'type' => Mystique::TEXT, // or InputfieldText 'useLanguages' => true, 'showIf' => [ 'window_title' => "!=''" ], 'attr' => [ 'placeholder' => __('Enter a navigation title') ] ], 'description' => [ 'label' => __('Description for search engines'), 'type' => Mystique::TEXTAREA, 'useLanguages' => true ], 'page_tpye' => [ 'label' => __('Type'), 'type' => Mystique::SELECT, 'options' => [ 'basic' => __('Basic page'), 'gallery' => __('Gallery'), 'blog' => __('Blog') ] ], 'show_on_nav' => [ 'label' => __('Display this page on navigation'), 'type' => Mystique::CHECKBOX ] ] ]; Searching data on Mystique field is limited. Because, Mystique saving data to database in json format. When you make search for Mystique field, operator not important. Operator will be changed with %= operator.
      Search example
      $navigationPages = pages()->find('my_mystique_field.show_on_nav=1'); $navigationPages = pages()->find('my_mystique_field.page_tpye=gallery');
×
×
  • Create New...