Bug 113974 - Ability to parse/filter JSON data and extract fields
Summary: Ability to parse/filter JSON data and extract fields
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2017-11-21 16:04 UTC by Dan Dascalescu
Modified: 2021-07-22 18:04 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Dan Dascalescu 2017-11-21 16:04:24 UTC
REST is far more popular than SOAP nowadays, and it's time to have a FILTERJSON function equivalent to FILTERXML.

Steps to Reproduce:
The vast majority of REST APIs return JSON data.

Actual Results:  
FILTERJSON (or "PARSEJSON") could use dotted notation to extract object fields.

Expected Results:
A1: =WEBSERVICE("https://api.github.com/repos/DmytroBazunov/LibreOfficeGetRestPlugin/issues/6")
B1: = PARSEJSON(A1, "user.login")  // "dandv"

Reproducible: Always

User Profile Reset: No

Additional Info:
There's a poorly maintained plugin that attempts to do this, https://github.com/DmytroBazunov/LibreOfficeGetRestPlugin/issues/6

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.89 Safari/537.36
Comment 1 Buovjaga 2017-11-23 10:19:23 UTC
Implementing this will be possible with the nearly-ready dataprovider: https://cgit.freedesktop.org/libreoffice/core/tree/sc/source/ui/dataprovider
Comment 2 Dan Dascalescu 2018-01-11 01:53:55 UTC
Nice, I see very recents commits.

As extra motivation, I was trying to extract Bitcoin prices from this API, 

=SUBSTITUTE(WEBSERVICE("https://min-api.cryptocompare.com/data/pricemulti?fsyms=BTC,ETH,LTC&tsyms=USD"), """:{""USD""", "")

The SUBSTITUTE above is just prep. After that, to get at a value, I came up with a formula looks like this:

=VALUE(MID(A1, SEARCH("(?<=BTC:)\d", A1), FIND("}", A1, SEARCH("(?<=BTC:)\d", A1)) - SEARCH("(?<=BTC:)\d", A1)))

A week later, that thing above looks more cryptic than elliptic curve cryptography :) I have no idea what it does at a glance and why it's so repetitive.