Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

------------------------------
Paul Archer
------------------------------

Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

Hi Paul,

it seems I cannot reply to your private massage (there is no reply button).

The problem seems to be, that you have to use "https:" instead of "http:".

The "http:" URL only returns a HTML answer "301 - Moved permanently" - HTTP_GET doesn't follow the redirection and returns the HTML string - and JSON_TABLE cannot handle this.

When debugging SQL you always have to work inside-out / bottom-up - so I tried "values qsys2.http_get('http://...);" and got the HTML 301 as result. So I tried with "https://..." and it returned JSON.

HTH

Daniel

Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

Hi Daniel

There is still something wrong at my end - I am glad that it's working for you though - when I go through the same HTTPS process I am still getting errors.

We are running at a 00 TR level for V7R3 so I am hoping that an upcoming OS update will resolve the problems. In the meantime I would like to thank you for taking the time to support my inquiry. You've been extremely patient and informative - I have learned a lot from this interaction.

With Sincere Thanks & Best Regards

Hi Paul,

it seems I cannot reply to your private massage (there is no reply button).

The problem seems to be, that you have to use "https:" instead of "http:".

The "http:" URL only returns a HTML answer "301 - Moved permanently" - HTTP_GET doesn't follow the redirection and returns the HTML string - and JSON_TABLE cannot handle this.

When debugging SQL you always have to work inside-out / bottom-up - so I tried "values qsys2.http_get('http://...);" and got the HTML 301 as result. So I tried with "https://..." and it returned JSON.

HTH

Daniel

Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

Hi Daniel

There is still something wrong at my end - I am glad that it's working for you though - when I go through the same HTTPS process I am still getting errors.

We are running at a 00 TR level for V7R3 so I am hoping that an upcoming OS update will resolve the problems. In the meantime I would like to thank you for taking the time to support my inquiry. You've been extremely patient and informative - I have learned a lot from this interaction.

With Sincere Thanks & Best Regards

------------------------------
Paul Archer

Original Message:
Sent: Wed July 24, 2024 02:05 PM
From: Daniel Gross
Subject: SQL into JSON_TABLE from Webservice

Hi Paul,

it seems I cannot reply to your private massage (there is no reply button).

The problem seems to be, that you have to use "https:" instead of "http:".

The "http:" URL only returns a HTML answer "301 - Moved permanently" - HTTP_GET doesn't follow the redirection and returns the HTML string - and JSON_TABLE cannot handle this.

When debugging SQL you always have to work inside-out / bottom-up - so I tried "values qsys2.http_get('http://...);" and got the HTML 301 as result. So I tried with "https://..." and it returned JSON.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Fri July 19, 2024 11:08 AM
From: Paul Archer
Subject: SQL into JSON_TABLE from Webservice

Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

------------------------------
Paul Archer
------------------------------

Dear Paul

For you to use the new HTTP_GET in SYS2 (as opposed to the old one in SYSTOOLS) in IBM i 7.3, you need to apply at least SF99703 Level 28 according to this IBM Technote: New HTTP functions based in QSYS2 at https://www.ibm.com/support/pages/new-http-functions-based-qsys2       

This article provides samples of the use of these SQL HTTP functions: New QSYS2.HTTP Functions SQL (7.4 TR5 and 7.3 TR11) at https://blog.faq400.com/en/db2-for-i/qsys2-http-functions-en/     

------------------------------
Satid S

Original Message:
Sent: Fri July 26, 2024 09:31 AM
From: Paul Archer
Subject: SQL into JSON_TABLE from Webservice

Hi Daniel

There is still something wrong at my end - I am glad that it's working for you though - when I go through the same HTTPS process I am still getting errors.

We are running at a 00 TR level for V7R3 so I am hoping that an upcoming OS update will resolve the problems. In the meantime I would like to thank you for taking the time to support my inquiry. You've been extremely patient and informative - I have learned a lot from this interaction.

With Sincere Thanks & Best Regards

------------------------------
Paul Archer

Original Message:
Sent: Wed July 24, 2024 02:05 PM
From: Daniel Gross
Subject: SQL into JSON_TABLE from Webservice

Hi Paul,

it seems I cannot reply to your private massage (there is no reply button).

The problem seems to be, that you have to use "https:" instead of "http:".

The "http:" URL only returns a HTML answer "301 - Moved permanently" - HTTP_GET doesn't follow the redirection and returns the HTML string - and JSON_TABLE cannot handle this.

When debugging SQL you always have to work inside-out / bottom-up - so I tried "values qsys2.http_get('http://...);" and got the HTML 301 as result. So I tried with "https://..." and it returned JSON.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Fri July 19, 2024 11:08 AM
From: Paul Archer
Subject: SQL into JSON_TABLE from Webservice

Hi Folks - I'm trying to get JSON data from:

https://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31

...JSON Data is being returned as an array:

[  {    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",    "startDate": "2024-01-01",    "endDate": "2024-01-01",    "type": "Public",    "name": [      {        "language": "EN",        "text": "New Year's Day"      }    ],    "nationwide": true  },  {    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",    "startDate": "2024-02-05",    "endDate": "2024-02-05",    "type": "Public",    "name": [      {        "language": "EN",        "text": "Saint Brigid's Day"      }    ],    "nationwide": true  }]

 If I take the above data and load it into a variable:

set json_var = 
'[
{
    "id": "a52297b3-caf7-48b6-bc68-03f973a2e8ce",
    "startDate": "2024-01-01",
    "endDate": "2024-01-01",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "New Years Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "09c316fe-6b3b-4f22-a988-dc2e6bb55e68",
    "startDate": "2024-02-05",
    "endDate": "2024-02-05",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Brigids Day"
      }
    ],
    "nationwide": true
  },
  {
    "id": "45bca84d-02e9-41b3-bf97-53c4e4892b12",
    "startDate": "2024-03-17",
    "endDate": "2024-03-17",
    "type": "Public",
    "name": [
      {
        "language": "EN",
        "text": "Saint Patricks Day"
      }
    ],
    "nationwide": true
  }
  ]';

...I'm able to parse the results:

SELECT *
FROM json_table(json_var, 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

 ==>

...I had to remove the apostrophe characters from the data but was able to get the expected results. However - when I replace the json_var with the actual API

SELECT *
FROM json_table('http://openholidaysapi.org/PublicHolidays?countryIsoCode=IE&languageIsoCode=EN&validFrom=2024-01-01&validTo=2024-12-31', 'lax $[*]' 
                        COLUMNS(
                        id VARCHAR(255) path 'lax $.id',
                        startDate VARCHAR(255) path 'lax $.startDate',
                        endDate VARCHAR(255) path 'lax $.endDate',
                        type VARCHAR(255) path 'lax $.type',
                        name varchar(20) path 'lax $.name.text'
                        ) error on error
                );

...and run it, I get the following:

...I am assuming that the issue is because of the apostrophes in the data...can someone please advise me on what I am doing wrong, and what I need to do to get this working?

Many Thanks

Paul

------------------------------
Paul Archer
------------------------------