From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: krishnamoorthi72@gmail.com
Subject: BUG #16623: JSON select query result is getting differed when we change DB version
Date: Fri, 18 Sep 2020 05:55:34 +0000
Message-ID: <16623-95eafdd48a3196e1@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16623
Logged by: Krishna R
Email address: krishnamoorthi72@gmail.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, krishnamoorthi72@gmail.com
Subject: Re: BUG #16623: JSON select query result is getting differed when we change DB version
In-Reply-To: <16623-95eafdd48a3196e1@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox