BUG #16623: JSON select query result is getting differed when we change DB version

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: krishnamoorthi72(at)gmail(dot)com
Subject: BUG #16623: JSON select query result is getting differed when we change DB version
Date: 2020-09-18 05:55:34
Message-ID: 16623-95eafdd48a3196e1@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16623
Logged by: Krishna R
Email address: krishnamoorthi72(at)gmail(dot)com
PostgreSQL version: 9.6.2
Operating system: Linux - Red Hat 4.8.5
Description:

Hi,
Could you please give your inputs on below issue that how we can proceed
further. We are moving our application which is currently using Postgres10.4
into Postgres9.6.2. Because of the application environment changes. All are
working fine but got struck with below issue.

Issue: When we read JSON array element values, Postgres10.4 is giving proper
response based on array elements index but Postgres9.6.2 is returning the
results like 'CROSS JOIN' outputs even retrieved from single column. Please
find below sample.

1. Create Table Script:
a. CREATE TABLE device_data_test (command_output json);

2. Insert Script:
INSERT INTO device_data_test
(command_output)
VALUES
('[
{
"name": "sample1",
"fvAp": [
{
"name": "fvAp1"
},
{
"name": "fvAp1.1"
}
]
},
{
"name": "sample2",
"fvAp": [
{
"name": "fvAp2"
}
]
}
]');

3. Select query results from Postgres10.4:

select json_array_elements(command_output)->>'name' as name,
json_array_elements(json_array_elements(command_output)->'fvAp')->>'name' as
appname from device_data_test;

name | appname
---------+---------
sample1 | fvAp1
sample1 | fvAp1.1
sample2 | fvAp2
(3 rows)

4. Select query results from Postgres9.6.2:

select json_array_elements(command_output)->>'name' as name,
json_array_elements(json_array_elements(command_output)->'fvAp')->>'name' as
appname from device_data_test;

name | appname
---------+---------
sample1 | fvAp1
sample2 | fvAp1.1
sample1 | fvAp2
sample2 | fvAp1
sample1 | fvAp1.1
sample2 | fvAp2
(6 rows)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Daniel Gustafsson 2020-09-18 09:38:15 Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
Previous Message PG Bug reporting form 2020-09-18 04:58:14 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column