0

I am developing the Spectra Analytics module on drupal.org, and one of the interesting new features in Drupal 8 is the ability to create "map" fields in the database. These fields are simply serialized data, such that you can save an associated array to the field, and Drupal will automatically serialize/unserialize the data.

For the main purpose of the module (querying by category and printing large amounts of custom data), this has been great. However, I am looking at applications where it might be useful to query the map data.

How can I do that?

Additional note:

I am reading up on MongoDB and other cases where this kind of data would be stored & query-able as a JSON array, so it seems like this is possible. One example application would be a list of alerts, and in a map field we have an associative array of data about the alert, such as "message," "severity," and "dismissed," and in this case we are looking for a particular "dismissed" property. The alternative solution is to use Field API fields, i.e. create an extra table to hold data that is queried in the traditional way.

5
  • You can’t query within serialized blobs, you can only do this if the table field is JSON type and use special functions of MySQL or Postgres to do this. But for regular serialized arrays, no. There are a couple of modules on Drupal displaying this as a proof of concept.
    – Kevin
    Aug 23, 2018 at 11:51
  • Right. I know it isn't possible to query serialized arrays with MySQL by default, which is why I'm looking at what changes I could do now, while this particular module (and possible future modules) are still in alpha. I could look at the JSON field case, and then test it versus using the Field API.
    – Mike Nolan
    Aug 23, 2018 at 14:41
  • I’ve tried it, it’s definitely possible. I POC’d it in drupal 7
    – Kevin
    Aug 23, 2018 at 15:07
  • 1
    For instance, note the db specific functions injected to db_query on this module: drupal.org/project/jsonb - I believe that was an example for Postgres. But MySQL has them now, too: dev.mysql.com/doc/refman/8.0/en/json-search-functions.html - if your database field is a JSON type, you can leverage all of this.
    – Kevin
    Aug 23, 2018 at 16:07
  • Thanks, I will look at the JSONB field module. Go ahead and make it an answer so I can upvote/accept it.
    – Mike Nolan
    Aug 23, 2018 at 16:56

1 Answer 1

1

OK, here's what I've developed so far. I did take Kevn's comment into account, and switched from the Map field to JSONB Field, but the example below does work on a map field if you remove the json_decode() part.

This is the render function for my data viewer, in which users can specify a key. The code will print_r() arrays, or return a string, depending on what it finds. If there is no key, it will print_r the entire field.

This doesn't cover querying, i.e. Views filters, just getting the data out for reporting.

/**
 * {@inheritdoc}
 */
public function render(ResultRow $values)
{
  $render_key = $this->options['spectra_data_render_key'];
  $data_entity = $this->getEntity($values);
  $data_m = $data_entity->get('data_data')->getValue();
  $data_map = isset($data_m[0]['value']) ? json_decode($data_m[0]['value'], TRUE) : FALSE;
  if ($data_map === FALSE) {
    return array(
      '#markup' => '',
    );
  }
  if (!$render_key) {
    return array(
      '#markup' => '<pre>' . print_r($data_map, TRUE) . '</pre>',
    );
  }
  else {
    if (isset($data_map[$render_key])) {
    $item = $data_map[$render_key];
      $markup = is_array($item) || is_object($item) ? '<pre>' . print_r($item, TRUE) . '</pre>' : (string) $item;
      return array(
        '#markup' => $markup,
      );
    }
    else {
      return array(
        '#markup' => '',
      );
    }
  }
}

Note: 'data_data' is a base field for a "spectra_data" entity, and $this->options['spectra_data_render_key'] is a custom field that is added to the settings for the Views field.

If the stored JSON is {"this": "that"}, then passing a key of "this" would return the string "that," and no key would return array("this" => "that").

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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