Skip to Main Content
  • Questions

  • How to get last item in a JSON array

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Summer.

Asked: March 18, 2020 - 12:59 pm UTC

Last updated: August 19, 2020 - 1:47 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi,

Our JSON object is stored as BLOB type in table. The JSON has nested array. Is it possible that I can get the last item for the nested array d? Is there a way that I can also get the size of the array d? 

select jt.*
from json_table ('{
  "a": 100,
  "b": 200,
  "c": [
    {
      "d": [
        {
          "x": "yes"
        }
      ]
    },
    {
      "d": [
        {
          "x": "no"
        },
        {
          "x": "yes"
        }
      ]
    }
  ]
}', '$' 
 columns (
   a number path '$.a',
   b number path '$.b',
   nested path '$.c.d[*]' columns (
     x varchar(16) path '$.x'
   )
 )) jt;


current output:
100 200 yes
100 200 no
100 200 yes

expected output:
100 200 yes
100 200 yes

Thanks for help!
Summer

and Chris said...

Oracle Database 18c added the size() JSON item-method. Which returns the number of elements in an array. Or one for scalars and objects.

In the meantime, you can add a "for ordinality" column to JSON_table. This returns the position of the element in the array. Though this is one-indexed, instead of zero-indexed like JSON arrays.

So:

- Add the position
- Find the number of elements in the array (either with size() or max(pos) over (...)
- Return rows where the position = size/max:

with jdata as (
  select '{
  "a": 100,
  "b": 200,
  "c": [
    {
      "d": [
        {
          "x": "yes"
        }
      ]
    },
    {
      "d": [
        {
          "x": "no"
        },
        {
          "x": "yes"
        }
      ]
    }
  ]
}' j from dual
), rws as (
  select jt.*, 
         max ( posd ) over (
           partition by posc
         ) d_mx_sz
  from   jdata j, json_table ( j, '$' 
   columns (
     a number path '$.a',
     b number path '$.b',
     csize number path '$.c.size()',
     nested path '$.c[*]' columns (
       dsize int path '$.d.size()',
       darr  format json path '$.d',
       posc for ordinality,
       nested path '$.d[*]' columns (
         posd for ordinality,
         x varchar2(10) path '$.x'
       ) 
     )
   )
  ) jt
)
  select * from rws
  where  posd = d_mx_sz; 
  
A      B      CSIZE    DSIZE    DARR                        POSC    POSD    X      D_MX_SZ   
   100    200        2        1 [{"x":"yes"}]                     1       1 yes             1 
   100    200        2        2 [{"x":"no"},{"x":"yes"}]          2       2 yes             2

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The answer is very useful!

Summer, March 19, 2020 - 5:26 pm UTC

Thanks a lot for providing the answer! This is very useful!

from 19c output.

Rajeshwaran, jeyabal, August 18, 2020 - 4:54 pm UTC

Team,

The below example was from 19c database.
inserted the above JSON into a table.
when i get "CSEQ" column in output, "DSEQ" columns output seems to be correct.
but when i dont get "CSEQ" column in output, "DSEQ" columns output seems to be incorrect.
Kinldy help us to undersand if this is a correct behaviour?

demo@PDB1> select jt.*
  2  from t, json_table( x, '$'
  3      columns(
  4          xa number path '$.a',
  5          xb number path '$.b',
  6          csize number path '$.c.size()',
  7          nested path '$.c[*]'
  8              columns(
  9                  dsize number path '$.d.size()' ,
 10                  cseq for ordinality ,
 11                  nested path '$.d[*]'
 12                  columns(
 13                      dx varchar2(10) path '$.x',dseq for ordinality)
 14                  )
 15          )) jt
 16  /

        XA         XB      CSIZE      DSIZE       CSEQ DX               DSEQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
       100        200          2          1          1 yes                 1
       100        200          2          2          2 no                  1
       100        200          2          2          2 yes                 2

demo@PDB1> select jt.*
  2  from t, json_table( x, '$'
  3      columns(
  4          xa number path '$.a',
  5          xb number path '$.b',
  6          csize number path '$.c.size()',
  7          nested path '$.c[*]'
  8              columns(
  9                  dsize number path '$.d.size()' ,
 10                  --cseq for ordinality ,
 11                  nested path '$.d[*]'
 12                  columns(
 13                      dx varchar2(10) path '$.x',dseq for ordinality)
 14                  )
 15          )) jt
 16  /

        XA         XB      CSIZE      DSIZE DX               DSEQ
---------- ---------- ---------- ---------- ---------- ----------
       100        200          2          1 yes                 1
       100        200          2          2 no                  2
       100        200          2          2 yes                 3

demo@PDB1> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


demo@PDB1>

Chris Saxon
August 19, 2020 - 1:47 pm UTC

The for ordinality column should start at one for each new parent, so yes this is incorrect.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.