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

Hi monks.

I'm just getting to grips with DBIx::Class and I'm trying to get it to work with jsonb data types in PostrgeSQL. I've got a simple select statement that returns a json string that I convert into a hash to pass to a template.

Simplified database: 
 Column |  Type   |                      Modifiers                    
+   
--------+---------+---------------------------------------------------
+---
 id     | integer | not null default nextval('details_id_seq'::regclas
+s)
 data   | jsonb   |

[download]
Dancer2 code: 
...
my $rs = schema->resultset('Detail')
                ->find({id => route_parameters->get('code')});
    if ($rs)
    {
        #return to_dumper(from_json($rs->data));
        template 'form', {'form' => from_json($rs->data)};
...

Decoded json: $VAR1 = { 'order_code' => '0165-02675-4565', 'email' => 
+'foo@test.com', 'price' => '15', 'title' => 'Mt Thing XYZ' };

[download]
Is there any way I can run queries based on keys/values in the json column using DBIx::Class, for instance where email = 'foo@...', or specifiy which key/vals I want to return?

I am doing this as I need a way to handle potentially unstructured data and I am restricted to using Postgres. I'm quite new to DBIx::Class, maybe this isn't the best tool to use in this case but I would appreciate any help.

Just found DBIx::NoSQL, which looks like it might be what I need but the docs say it's still in beta which is a concern.

Replies are listed 'Best First'.
Re: Using DBIx Class with json data types
by erix (Prior) on May 08, 2016 at 23:21 UTC

    PostgreSQL can index columns of the json or jsonb data type. On a 3.5 GB table I see a speedup from indexing, from ~4 seconds (seqscan) to 4 ms (GIN index).

    (this is using Postgres 9.5 but 9.4 should work similar, and 9.6 (beta coming out this week) will just add parallel search to the seqscan.)

    I did that test with synthetic data of roughly the form that you gave: 

     { 'order_code' => '0165-02675-4565', 'email' => 'foo@test.com', 'pric
+e' => '15', 'title' => 'Mt Thing XYZ' };

    [download]

    where I only varied the email adress.

    With the CREATE INDEX statement: 

    create index junk7_gin_idx using gin (data);

    [download]

    and the search statement: 

    select * from junk7 where data @> '{ "email": "abcd@text.com" }';

    [download]

    FWIW, below is a bash test I was playing with; perhaps there is something useful in there for you.

    Read more... (10 kB)
[reply]
[d/l]
[select]
Re: Using DBIx Class with json data types
by Your Mother (Archbishop) on May 08, 2016 at 21:12 UTC

    One thing you can do that simplifies part of the problem is use inflation/deflation on the column–

    package MySchema::Result::Detail;
use JSON::XS;
# Class setup, etc.
__PACKAGE__->inflate_column( data => {
    inflate => sub { decode_json( +shift ) },
    deflate => sub {
        # Takes data structure or plain JSON string.
        my $json = shift;
        ref $json ? encode_json($json) : $json;
    },
});

    [download]

    This doesn’t let you search into it though, just makes the transition from JSON to perl data seamless/DWIW. I’m with Corion about searching unstructured data. That said, you can pass literal SQL to DBIC so if Pg lets you do it, you sure can here and you could use resultsets to make it semantic/less-messy in code. I don’t have experience with this to rattle off any snippets today but it sounds like a fun thing to play with.

    Update: s/let’s/lets/; # DERP

[reply]
[d/l]
[select]
      Thanks, this was helpful. I probably wasn't that clear about what I'm trying to achieve, it's a work in progess and I'm working through options.
[reply]
Re: Using DBIx Class with json data types
by Corion (Patriarch) on May 08, 2016 at 17:47 UTC

    If you have "unstructured data", you're in for a hellish ride. It's usually much better to enforce the structure beforehand and set up a process by which the structure schema can be expanded in a quick and not too formal way.

    I think that Postgres nowadays even has extensions to run (XPath?) queries against the jsonb data type, but I wouldn't know how DBIx::Class can be coerced into actually creating the SQL needed to query that. The Postgres documentation on JSON (as found via Google) shows the SQL needed for querying data that is only available in the jsonb column.

    Update: Updated Pg doc link to the current version.

[reply]
Re: Using DBIx Class with json data types
by Anonymous Monk on Jan 20, 2017 at 10:37 UTC
    ->search( { 'json_custom' => \'->>\'status\' = \'1\'' } )
[reply]

Back to Seekers of Perl Wisdom