store/query semi-structure data

Hosen1989 has asked for the wisdom of the Perl Monks concerning the following question:

Dear ALL, We had application written in perl that create complex data structure for our subscriber (we have move than 4m subscribers).
each subscriber have some conmen fields that are are present in all of them and some other subscriber has missing some.
the data looks like the next:

%subscribers = {
    "user_001" = {
        "name" => "sam",
        "age" => "13",
        "color" =>['red','blue']
        "item"=>{
            "old" =>['PC','pen'],
            "new" =>['tap','car']
        },
    "user_002" = {
        "name" => "ali",
        "age" => "54",
        "color" =>['red','null','green']
        "item"=>{
            "old" =>['phone','TV']
        },
    "user_003" = {
        "name" => "foo",
        "age" => "02",
        "item"=>{
            "old" =>['']
        },
        ....
    }
}

#[our data are more nasty and complex]
[download]

now we try to store these data in DB then do some query in them like get user that have new 'TAPs' in item or there age is larger than 30 years.
what we need to know is:
What is the best method to store the data (as MySQL or Oracle db not option), we need something for semi-structure data.
How to do these queries taken in mind the preformence.

We jast need headline to start our search (and yes we did our homework using Google ^_^).

BR
Hosen

Comment on store/query semi-structure data Download Code

Replies are listed 'Best First'.
Re: store/query semi-structure data by erix (Prior) on May 24, 2015 at 20:51 UTC
MySQL or Oracle db not option http://www.postgresql.org/ is always an option. Using the jsonb datatype, perhaps? (and of course: DBI and DBD::Pg)	[reply]
Re: store/query semi-structure data by pme (Monsignor) on May 24, 2015 at 21:27 UTC
What about http://www.sqlite.org/? Perl wrapper module is available DBD::SQLite.	[reply]
Re: store/query semi-structure data by afoken (Chancellor) on May 24, 2015 at 20:59 UTC
What is the best method to store the data (as MySQL or Oracle db not option), we need something for semi-structure data. ... We jast need headline to start our search (and yes we did our homework using Google ^_^). Try searching for NoSQL. MongoDB may be interesting (haven't use it, yet). Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re: store/query semi-structure data by locked_user sundialsvc4 (Abbot) on May 25, 2015 at 12:30 UTC
I generally approach problems like this as having two distinct parts: (1) “storing it,” and then (2) “efficiently searching it.” The technique that I most-frequently adopt for storing the data is to use YAML, stored in a long-text field. The simple reason for this is, that it is human-readable. (Also, I have had various problems with Storable over the years, where I found that for whatever reason I could not exactly “thaw” what had been “frozen.”) For the second part of the problem, I build additional tables which contain pure search-fields ... metadata, listing the various records where a particular value-of-interest might be found. The logic which encodes-and-stores a record, also updates these metadata tables. Searches involve SQL queries to determine the list of records where a desired value might be, followed by individual retrieval and decoding of the candidate-records (using procedural logic) to determine where the values actually are.