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)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 109073 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency tree / graph
 
Reported: 2017-11-21 16:04 UTC by Dan Dascalescu
Modified: 2023-04-17 05:37 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

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 
Description:
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.
Comment 3 Kevin Suo 2021-10-21 15:46:37 UTC 
LibreOffice uses liborcus as an mandatory external lib, and Orcus provides json support. Thus this filer may not be hard to implement.
Comment 4 Kevin Suo 2021-10-21 15:50:35 UTC 
*** Bug 109073 has been marked as a duplicate of this bug. ***
Comment 5 Dan Dascalescu 2021-11-29 13:36:45 UTC Comment hidden (obsolete)
Comment 6 Buovjaga 2021-11-29 13:48:25 UTC 
(In reply to Dan Dascalescu from comment #5)
> 4 years later, any progress?

Yes, data provider got a UI: https://libreoffice-dataproviders.blogspot.com/2021/08/data-providers.html

But: "The external data currently supported are CSV , HTML , XML and Base". So what Kevin said in comment 3 is still relevant.
Comment 7 Melroy 2022-07-23 23:48:29 UTC 
Still really waiting on FILTERJSON() function. Please add it to LibreOffice! PLEASE GOD!
Comment 8 m.a.riosv 2023-01-21 00:20:53 UTC 
As in many cases, web-pages to import are in JSON format, it will be really useful.