Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Need something like a SQL query for JSON data structures (JSON::XS)

by walkingthecow (Friar)
on Sep 06, 2013 at 05:34 UTC ( #1052651=perlquestion: print w/ replies, xml ) Need Help??
walkingthecow has asked for the wisdom of the Perl Monks concerning the following question:

I have JSON data that looks like this:
{ "People" : [ { "name" : "bob", "title" : "janitor", "email" : "taco@blah.com", "iq" : "180", "favorite_food" : "wagyu steak" }, { "name" : "joe", "title" : "software engineer", "email" : "", "iq" : "80", "favorite_food" : "raw hamburger" }, { "name" : "sandy", "title" : "dishwasher", "email" : "", "iq" : "240", "favorite_food" : "tacos" }, { "name" : "george", "title" : "software engineer", "email" : "", "iq" : "14", "favorite_food" : "tacos" } ] }
I am trying to filter this data in such a way that it returns any arrays whose values (any value) matches a word. So, using the above example, say that someone was to want to filter this out on the word taco, that filter would return this:
{ "People" : [ { "name" : "bob", "title" : "janitor", "email" : "taco@blah.com", "iq" : "180", "favorite_food" : "wagyu steak" }, { "name" : "sandy", "title" : "dishwasher", "email" : "", "iq" : "240", "favorite_food" : "tacos" }, { "name" : "george", "title" : "software engineer", "email" : "", "iq" : "14", "favorite_food" : "tacos" } ] }
It returned anything where a value matches "taco." Think of it like this: Each key/value pair is a column in a SQL table, and I am trying to say
SELECT * FROM People WHERE name = 'taco' OR title = 'taco' OR email = +'taco' OR iq = 'taco' OR favorite_food eq 'taco';
I'd really like to do this matching with grep, but just knowing how to do it at all will be a start in the right direction (if it is possible).

Comment on Need something like a SQL query for JSON data structures (JSON::XS)
Select or Download Code
Re: Need something like a SQL query for JSON data structures (JSON::XS)
by hdb (Prior) on Sep 06, 2013 at 05:53 UTC

    If you read your data with JSON, you can use grep in the intended way, see below. However, the SQL you provided would not return any records for the example data as none of the fields equals 'taco'. So I have been using matching instead of test for equality below and I am testing only two fields. HTH.

    use strict; use warnings; use Data::Dumper; use JSON; my $json = <<'JSON'; { "People" : [ { "name" : "bob", "title" : "janitor", "email" : "taco@blah.com", "iq" : "180", "favorite_food" : "wagyu steak" }, { "name" : "joe", "title" : "software engineer", "email" : "", "iq" : "80", "favorite_food" : "raw hamburger" }, { "name" : "sandy", "title" : "dishwasher", "email" : "", "iq" : "240", "favorite_food" : "tacos" }, { "name" : "george", "title" : "software engineer", "email" : "", "iq" : "14", "favorite_food" : "tacos" } ] } JSON my $j = JSON->new->decode($json); my @results2 = grep { $_->{email} =~ /taco/ or $_->{favorite_food} =~ +/taco/ } @{ $j->{People} }; print Dumper \@results2;
      Thank you! Thank you! Thank you!!! That perfectly solved my issue; though I'm not entirely sure how to grep the value of all keys without knowing what they keys are named, but that much I can figure out. I really appreciate the fast help! Thanks again ;)

      Update

      Figured it out:

      #!/usr/bin/env perl use strict; use warnings; use Data::Dumper; use JSON::XS; use File::Slurp qw(read_file); my $lines = decode_json( read_file("test.json") ); my @results = grep { grep { $_ =~ /taco/ } values %$_ } @{ $lines }; print Dumper \@results;
      Thanks again hdb!

        With a simple hash, to grep the keys:

        my @results = grep /taco/, keys %hash;

        To grep the values:

        my @results = grep /taco/, values %hash;

        To get the keys whose values match a regex:

        my @results = map {$hash{$_} =~ /taco/ ? $_:  ()} keys %hash;
Re: Need something like a SQL query for JSON data structures (JSON::XS) (PostgreSQL)
by erix (Vicar) on Sep 06, 2013 at 10:42 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1052651]
Approved by hdb
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (6)
As of 2014-11-28 11:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (196 votes), past polls