Indexing and Searching `json` data type

I’m developing a Laravel-based web application that has the Post model -

id - bigInt
slug - varchar
data - json
created_at - timestamp
updated_at - timestamp

The data column is of json data type because I want to store multiple, dynamic records in it.

I’m curious to know how do I convert the json data before I feed it to Algolia’s APIs? I’ve tried casting the data attribute to array, but it doesn’t seem to work.

My data attribute stores multidimensional arrays encoded as JSON; and I cannot predict the structure of the data being stored (that’s why the json data type).

Should I switch over to storing the data as json strings in text column? Or is there any way to feed the JSON data to Algolia?

Thank you in advance for your time.

1 Like

Hi kaustubh,

I see this is your first post on the forum - welcome to our community!

To clarify, Algolia is able to index any JSON data.

I found this previous topic that seems to match closely what you’re asking for:

Also, this section of the Laravel documentation about casting DB-JSON properties to array might be useful:
https://laravel.com/docs/5.3/eloquent-mutators#array-and-json-casting

Best regards

1 Like

Hello @jerome.schneider , thank you for your response.

I did check the thread you’ve linked to. However, the accepted solution converts each data item to string using explode function.

public function toSearchableArray()
    {
        $data = $this->toArray();

        $data['grades'] = explode(';', $data['grades']);
        $data['units'] = explode(';', $data['units']);

        return $data;
    }

In my case, however, I do not have the liberty to do that. This is because the data (json) column in my database contains unstructured data; and I cannot extract the keys.

In my search, however, I’d want to search inside json data and return the results. My current working solution is to cast the data attribute as string. Meaning, entire unstructured data will be fed to Algolia as a string.

I wish to know if this is the right way? Or is there any better approach?