Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

convert JSON to JSON Paths -- try not to reinvent the wheel (?)

by leszekdubiel (Scribe)
on Apr 25, 2020 at 20:01 UTC ( [id://11116061]=perlquestion: print w/replies, xml ) Need Help??

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

Hello Monks! If could anyone during perl-meditation put a light on the problem, then I would be grateful...

Background: there are some multi-level structured data in JSON for our internal web page. I told marketing people that the data is in JSON and they could edit that data in Notepad++. They are not happy with that. They want Excel. So I decided that I will convert JSON to Excel, they will edit Excel table, and I will read that table back to JSON data. Me and marketing people agreed to have complicated column names... -- these column names would be just JSON Paths.

So I need to read JSON data, and emit pairs -- JSON path and value under this path. Here is the program to write such data.

What is my question? Where do I need help? Is there a simpler way of doing such conversion JSON --> path/values? How to tell Perl to dive into JSON and find all simple data (scalars...) and return paths and values of them...

#!/usr/bin/perl -CSDA use utf8; use Modern::Perl qw{2019}; use JSON; use Data::Dumper; sub extract_paths_values { my ($cur_node, $top_path, $put_here) = @_; $top_path //= "\$"; $put_here //= {}; if (ref $cur_node eq "HASH") { extract_paths_values($$cur_node{$_}, "$top_path\['$_'\]", $put +_here) for keys %$cur_node; } elsif (ref $cur_node eq "ARRAY") { extract_paths_values($$cur_node[$_], "$top_path\[$_\]", $put_h +ere) for 0 .. @$cur_node - 1; } else { $$put_here{$top_path} = $cur_node; } return $put_here; } my $json = ' { "store" : { "files": [ ["alfa.txt", "about first letter in greek"], ["model.dxf", "for architects"], ["errors.txt", "no need for that normally"], ], "book" : [ { "category" : "reference", "title" : "Sayings of the Century", "author" : "Nigel Rees", "price" : 8.95 }, { "author" : "Evelyn Waugh", "title" : "Sword of Honour", "price" : 12.99, "category" : "fiction" }, ], "bicycle" : [ { "price" : 19.95, "color" : "red" } ] }, "status" : { "curr" : "Okey", "code" : 232, } } '; my $paths_values = extract_paths_values( JSON->new->relaxed->decode($json) ); print "$_ ~~~~> $$paths_values{$_}\n" for sort keys %$paths_values; print "Hurray! Done that \n";

result:

$['status']['code'] ~~~~> 232 $['status']['curr'] ~~~~> Okey $['store']['bicycle'][0]['color'] ~~~~> red $['store']['bicycle'][0]['price'] ~~~~> 19.95 $['store']['book'][0]['author'] ~~~~> Nigel Rees $['store']['book'][0]['category'] ~~~~> reference $['store']['book'][0]['price'] ~~~~> 8.95 $['store']['book'][0]['title'] ~~~~> Sayings of the Century $['store']['book'][1]['author'] ~~~~> Evelyn Waugh $['store']['book'][1]['category'] ~~~~> fiction $['store']['book'][1]['price'] ~~~~> 12.99 $['store']['book'][1]['title'] ~~~~> Sword of Honour $['store']['files'][0][0] ~~~~> alfa.txt $['store']['files'][0][1] ~~~~> about first letter in greek $['store']['files'][1][0] ~~~~> model.dxf $['store']['files'][1][1] ~~~~> for architects $['store']['files'][2][0] ~~~~> errors.txt $['store']['files'][2][1] ~~~~> no need for that normally Hurray! Done that :)

Replies are listed 'Best First'.
Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by roboticus (Chancellor) on Apr 25, 2020 at 20:08 UTC

    leszekdubiel:

    CPAN has JSON::Path which looks like it could do ?most/all? of what you need.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by haukex (Archbishop) on Apr 25, 2020 at 20:10 UTC
    What is my question? Where do I need help? Is there a simpler way of doing such conversion JSON --> path/values? How to tell Perl to dive into JSON and find all simple data (scalars...) and return paths and values of them...

    I actually think your code is fine. I once showed something related here, with somewhat simpler names for the paths. Also, this is the perfect case for unit tests, so you can be sure it does what you want.

Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by 1nickt (Canon) on Apr 25, 2020 at 20:47 UTC
Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by leszekdubiel (Scribe) on Apr 25, 2020 at 20:56 UTC
    Ok! Thank your for opinions. JSON::Path and JSON:Pointer do lookup/set/check... My goal is to list all possible paths from JSON.
Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by NERDVANA (Deacon) on Apr 26, 2020 at 21:56 UTC
Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by Anonymous Monk on Apr 26, 2020 at 22:44 UTC
Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by leszekdubiel (Scribe) on May 29, 2020 at 15:33 UTC

    Here is my final solution to flattening JSON. I abandoned JSON Path, because keys were too complicated for headers in Excel file. I have used  ➔ (space, arrow, space) to separate elements of path. Elements of path are encoded as ordinary scalar JSON data, but inside texts arrows are change to double arrows (this way they never contain single arrow with spaces around).

    Here is the function for flattening:

    sub flattened_json { my ($cur_node, $top_path, $put_here) = @_; $top_path //= []; $put_here //= {}; if (ref $cur_node eq "HASH") { flattened_json($$cur_node{$_}, [ @$top_path, $_ ], $pu +t_here) for sort keys %$cur_node; } elsif (ref $cur_node eq "ARRAY") { flattened_json($$cur_node[$_], [ @$top_path, $_ ], $pu +t_here) for 0 .. @$cur_node - 1; } else { $$put_here{ join " ➔ ", map { $json_do->pretty(0)->encode($_) =~ s/&#1 +0132;/➔➔/gr } @$top_path } = $json_do->pretty(0)->encode($cur_node) =~ s/&#1013 +2;/➔➔/gr; } return $put_here; }

    Here is the tool to replace JSON to paths with arrows json_to_paths:

    #!/usr/bin/perl -CSDA use utf8; use Modern::Perl qw{2017}; use JSON; use Text::Unidecode; require "./narz&#281;dzia"; my $f = flattened_json(JSON->new->relaxed->decode(do { local $/; <STDI +N> })); print "$_ &#10132; $$f{$_}\n" for sort { lc(unidecode($a =~ s/ &#10132; ([0-9]+)/sprintf " &#10132; %09 +d", $1/ger)) cmp lc(unidecode($b =~ s/ &#10132; ([0-9]+)/sprintf " &#10132; %09 +d", $1/ger)) } keys %$f;

    and here is the tool paths_to_json to replace paths back to json:

    #!/usr/bin/perl -CSDA use utf8; use Modern::Perl qw{2017}; use JSON; use Text::Unidecode; require "./narz&#281;dzia"; my $f = flattened_json(JSON->new->relaxed->decode(do { local $/; <STDI +N> })); print "$_ &#10132; $$f{$_}\n" for sort { lc(unidecode($a =~ s/ &#10132; ([0-9]+)/sprintf " &#10132; %09 +d", $1/ger)) cmp lc(unidecode($b =~ s/ &#10132; ([0-9]+)/sprintf " &#10132; %09 +d", $1/ger)) } keys %$f;

    It works like this:

    # cat json.txt { "menu" : { "header" : "SVG Viewer", "items" : [ { "id" : "Open" }, { "id" : "OpenNew", "label" : "Open New" }, null, { "id" : "ZoomIn", "label" : "Zoom In" }, { "id" : "OriginalView", "label" : "Original View" } ] } } # cat json.txt | ./json_to_paths "menu" &#10132; "header" &#10132; "SVG Viewer" "menu" &#10132; "items" &#10132; 0 &#10132; "id" &#10132; "Open" "menu" &#10132; "items" &#10132; 1 &#10132; "id" &#10132; "OpenNew" "menu" &#10132; "items" &#10132; 1 &#10132; "label" &#10132; "Open New +" "menu" &#10132; "items" &#10132; 2 &#10132; null "menu" &#10132; "items" &#10132; 3 &#10132; "id" &#10132; "ZoomIn" "menu" &#10132; "items" &#10132; 3 &#10132; "label" &#10132; "Zoom In" "menu" &#10132; "items" &#10132; 4 &#10132; "id" &#10132; "OriginalVie +w" "menu" &#10132; "items" &#10132; 4 &#10132; "label" &#10132; "Original + View" # cat json.txt | ./json_to_paths | ./paths_to_json { "menu" : { "header" : "SVG Viewer", "items" : [ { "id" : "Open" }, { "id" : "OpenNew", "label" : "Open New" }, null, { "id" : "ZoomIn", "label" : "Zoom In" }, { "id" : "OriginalView", "label" : "Original View" } ] } }

    PS. unfortunatelly perlmonks engine replaced arrows and special unicode chars with html entities... :( But I post anyway if anyone ever had the same problem

Re: convert JSON to JSON Paths -- try not to reinvent the wheel (?)
by Anonymous Monk on Apr 28, 2020 at 02:31 UTC
    I think I'd put the paths into a row of cells and then hide that row ...
      Brilliant. And then make the spreadsheeet read only. And then delete it. Maximum sharing. NOT

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11116061]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2024-03-28 16:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found