How can I evaluate all values of a path with Oracle?

I'm working with Oracle's sample schemas.

With the following column values of PRODUCT_DTAILS,

"{
  "colour" : "brown",
  "gender" : "Women's",
  "brand" : "PROTODYNE",
  "description" : "Est dolor tempor sint commodo irure sint ut dolor proident enim Lorem. Pariatur deserunt nostrud quis minim non.",
  "sizes" :
  [
    0,
    2,
    4,
    6,
    8,
    10,
    12,
    14,
    16,
    18,
    20
  ],
  "reviews" :
  [
    {
      "rating" : 2,
      "review" : "Occaecat cupidatat in id elit magna Lorem esse ad magna labore non qui magna."
    },
    {
      "rating" : 8,
      "review" : "Cupidatat cupidatat laboris consectetur labore veniam aliqua et incididunt duis sunt proident."
    },
    {
      "rating" : 2,
      "review" : "Esse ipsum veniam ullamco irure ad minim mollit consequat non dolor labore."
    },
    {
      "rating" : 1,
      "review" : "Cillum ea minim voluptate id ut consectetur commodo nostrud cillum eiusmod eiusmod dolore cillum veniam."
    },
    {
      "rating" : 5,
      "review" : "Excepteur adipisicing culpa dolor id et irure sint ex non nostrud velit pariatur esse quis."
    },
    {
      "rating" : 9,
      "review" : "Do fugiat aliqua sunt quis proident fugiat."
    }
  ]
}"
"{
  "colour" : "green",
  "gender" : "Women's",
  "brand" : "FLYBOYZ",
  "description" : "Qui aliquip dolor aute labore amet nostrud deserunt nulla ut veniam id. Ut aute velit tempor anim ex sit nisi.",
  "sizes" :
  [
    0,
    2,
    4,
    6,
    8,
    10,
    12,
    14,
    16,
    18,
    20
  ],
  "reviews" :
  [
    {
      "rating" : 7,
      "review" : "Mollit consequat minim sit consequat deserunt duis."
    },
    {
      "rating" : 8,
      "review" : "Quis eu esse proident elit eu aliqua magna voluptate labore adipisicing voluptate ex do."
    },
    {
      "rating" : 6,
      "review" : "Laborum nulla aliquip nulla adipisicing aliquip qui cupidatat aliquip in."
    },
    {
      "rating" : 3,
      "review" : "Exercitation aute voluptate voluptate tempor sit enim ut veniam do."
    },
    {
      "rating" : 8,
      "review" : "Cillum cillum anim aliqua eu deserunt amet eu ut veniam in qui."
    },
    {
      "rating" : 7,
      "review" : "Nostrud aliqua ullamco irure consectetur elit nisi eu elit reprehenderit ut."
    },
    {
      "rating" : 5,
      "review" : "Irure nisi dolore dolore ut non ad minim pariatur."
    },
    {
      "rating" : 2,
      "review" : "Laboris aliqua sint est incididunt sunt non tempor irure reprehenderit labore."
    }
  ]
}"
"{
  "colour" : "blue",
  "gender" : "Boy's",
  "brand" : "WRAPTURE",
  "description" : "Id sit adipisicing ea dolore fugiat laborum ut dolore. Reprehenderit aliqua non adipisicing aliqua adipisicing aute ullamco consectetur est aliqua.",
  "sizes" :
  [
    "1 Yr",
    "2 Yr",
    "3-4 Yr",
    "5-6 Yr",
    "7-8 Yr",
    "9-10 Yr"
  ],
  "reviews" :
  [
    {
      "rating" : 7,
      "review" : "Laborum labore exercitation culpa sint cillum aute duis labore do excepteur."
    },
    {
      "rating" : 10,
      "review" : "Do velit laborum adipisicing velit."
    },
    {
      "rating" : 6,
      "review" : "Culpa dolor aute adipisicing ad."
    },
    {
      "rating" : 6,
      "review" : "Sit sunt elit proident fugiat consectetur id incididunt nulla nulla magna consectetur."
    },
    {
      "rating" : 6,
      "review" : "Adipisicing ipsum eiusmod sint ullamco dolor irure qui officia."
    },
    {
      "rating" : 4,
      "review" : "Ipsum commodo amet non ut labore."
    }
  ]
}"
"{
  "colour" : "grey",
  "gender" : "Boy's",
  "brand" : "HANDSHAKE",
  "description" : "Tempor laborum voluptate mollit aliquip et tempor nostrud Lorem. Nostrud anim exercitation est fugiat elit est deserunt mollit exercitation.",
  "sizes" :
  [
    "1 Yr",
    "2 Yr",
    "3-4 Yr",
    "5-6 Yr",
    "7-8 Yr",
    "9-10 Yr"
  ],
  "reviews" :
  [
    {
      "rating" : 8,
      "review" : "Quis culpa laborum ex magna."
    },
    {
      "rating" : 3,
      "review" : "Culpa ullamco deserunt ex ea."
    },
    {
      "rating" : 3,
      "review" : "Fugiat ullamco reprehenderit tempor nulla ad fugiat qui excepteur sunt officia deserunt nulla."
    },
    {
      "rating" : 2,
      "review" : "Mollit dolore magna magna veniam culpa ullamco tempor esse id in occaecat excepteur ullamco ea."
    },
    {
      "rating" : 10,
      "review" : "Culpa dolore enim consequat aliquip nulla ipsum."
    },
    {
      "rating" : 2,
      "review" : "Excepteur aliqua sunt exercitation mollit pariatur anim tempor."
    },
    {
      "rating" : 8,
      "review" : "Proident culpa tempor dolore deserunt anim ea deserunt quis duis."
    },
    {
      "rating" : 8,
      "review" : "Reprehenderit est do quis quis reprehenderit adipisicing qui Lorem mollit sit labore veniam."
    },
    {
      "rating" : 1,
      "review" : "Mollit dolore ad laboris ut cillum velit in sint labore nulla Lorem minim."
    }
  ]
}"

I wan to select distinct values of $.reviews[*].rating.

SELECT DISTINCT JSON_VALUE(PRODUCT_DETAILS, '$.reviews[*].rating')
FROM PRODUCTS
;

<null>
5
7
10

SELECT DISTINCT JSON_QUERY(PRODUCT_DETAILS, '$.reviews[*].rating')
FROM PRODUCTS
;

<null>
5
7
10

2, 3, 8, and so on are missing.

What did I do wrong?, wrong function? wrong expression?

In this case JSON_QUERY requires to specify "with array wrapper" JSON_QUERY(PRODUCT_DETAILS, '$.reviews[*].rating' with array wrapper) then it will return the array of values, but since it doesn't yet support DISTINCT you better use the JSON_TABLE solution (that gives you the opportunity to sort the results)

OR

if you have 23 and you enabled MLE support:

create or replace function js_as_sortedset(inArr JSON) RETURN JSON 
AS MLE LANGUAGE JAVASCRIPT 
{{
    /* up to you to decide if you prefer returning NULL if input is null */
    var res = INARR == null ? [] : [...new Set(INARR)];
    return res.sort();
}};


select js_as_sortedset(JSON_QUERY(product_details, '$.reviews[*].rating' with array wrapper)) as v
from data
;