Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Parsing a JSON field

I have a Qlik connection with a PostgreSQL database. I have a table called 'archive' storing a Date, a platform name and a JSON with the following structure

[
    {
        "date": "2019-04-04",
        "agency": "aaa",
        "revenue": 55
    },
    {
        "date": "2019-04-04",
        "agency": "bbb",
        "revenue": 45
    },
    {
        "date": "2019-04-03",
        "agency": "aaa",
        "revenue": 52
    },
    {
        "date": "2019-04-03",
        "agency": "bbb",
        "revenue": 42
    }
]

I am trying to have each one of these records as a row with this structure :

id, date, platform, record_date*, agency*, revenue* with the *fields coming from the Json. 

I tried messing around with the load editor but I can not figure out how to do so, is there any function allowing me to parse this JSON field ? Should I change the structure of those JSON to make this possible ?

Thanks and have a good day.

Labels (3)
3 Replies
Highlighted
Partner
Partner

There is a script function to parse a JSON field. Here is an example:

Table:
load '[
    {
        "date": "2019-04-04",
        "agency": "aaa",
        "revenue": 55
    },
    {
        "date": "2019-04-04",
        "agency": "bbb",
        "revenue": 45
    },
    {
        "date": "2019-04-03",
        "agency": "aaa",
        "revenue": 52
    },
    {
        "date": "2019-04-03",
        "agency": "bbb",
        "revenue": 42
    }
]' as JSON
AutoGenerate (1);

load *
FROM_FIELD
(Table, JSON)
(json, utf8, no labels);

drop table Table;

Hope this helps.

Highlighted
Contributor III
Contributor III

@fosuzuki How I can join those fields, which they are obtained from JSON field, with other tables?

I am working with several fields from JSON format. I use FROM_FIELD instruction but I have to join with other fields and I don't know how I have to do it, because there is not common fields.

Thank you very much.

Highlighted
Partner
Partner

I'm not sure if I understood what you need to do.

To me, it only makes sense to join two tables if they have at least one field in common.

Can you post a sample of your tables and fields?