BUG #16623: JSON select query result is getting differed when we change DB version - Mailing list pgsql-bugs

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)