1

I've built a statement where the result is spooled into a .json file.

With SQLcl, it starts off as:

SET ENCODING UTF-8
SET SQLFORMAT JSON
SPOOL language.json

Then, I do selects. Just an example:

SELECT
    INITCAP(ltl.language) language,

// ... goes on

Output:

"language":"En-Us"

This will capitalize the value of language -- however, what if I want language to be Language in the .json dump?

What I have tried is the following:

SELECT
    ltl.language INITCAP(language),

// ... goes on

But that isn't a valid query.

Also, changing language to Language in the query makes no difference - it's transformed to lowercase:

SELECT
    INITCAP(ltl.language) Language,

Output:

"language":"en-US"

My desired output is:

Output:

"Language":"en-US"

How do I achieve that from the query?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
cbll
  • 6,499
  • 26
  • 74
  • 117
  • 2
    use quotation marks like `"Language"` – hotfix Aug 16 '18 at 12:56
  • Could you elaborate? I don't think that's a valid `SELECT`, if I am understanding you – cbll Aug 16 '18 at 13:00
  • @hotfix - I don't think that works; it doesn't in SQL Developer 18.2 anyway - the JSON name is still lowercase. I don't have the latest SQLcl handy to verify there. [It seems to be a known issue though](https://community.oracle.com/thread/4147615). One for Jeff maybe... – Alex Poole Aug 16 '18 at 13:00
  • @cbll - hotfix means 'SELECT INITCAP(ltl.language) as "Language", ...`. Worth tryign but as I said it doesn't seem to work in this case. – Alex Poole Aug 16 '18 at 13:02
  • @AlexPoole i have sqldeveloper 17.4 and it works, also in sqlplus it works fine. i don't know what Version i have – hotfix Aug 16 '18 at 13:02
  • @hotfix - SQL\*Plus doesn't support `set sqlformat json`... I know aliasing the column like that works, the issue is that the JSON formatting always lower-cases it. Or does that work in 17.4 and has broken in 18? – Alex Poole Aug 16 '18 at 13:09
  • @AlexPoole I attempted it - doesn't work unfortunately, DB throws a default error – cbll Aug 16 '18 at 13:16
  • @AlexPoole actually it doesn't work in 17.4 too. i couldn't find any information on metalink. – hotfix Aug 16 '18 at 13:21
  • 1
    per our oracle JSON spec here at oracle the JSON keys are lowercase, there's no way to change that in SQLcl/SQLDev. You'd have build your own json generator, or change the output after the fact with some regex/perl/python/whatever – thatjeffsmith Aug 16 '18 at 13:46
  • 1
    @thatjeffsmith Thanks, I was gonna resort to writing a script that transforms the keys as intended - just wouldn't wanna put in the effort until I was certain. Feel free to respond this and I'll accept it as an answer. – cbll Aug 16 '18 at 13:50

2 Answers2

3

You cannot influence the case of the json value pair KEYS generated when using /*json*/ or SQLFORMAT json. Per our Oracle spec, those are lowercase by design.

You would need to build your own JSON generator, or write some shell scripts with RegEX or whatever you find handy to go in and init cap the keys after-the-fact.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
1

You can use regexp_replace() function.

I don't know how you are composing your final JSON payload, but the workaround would be something like this:

select regexp_replace('"language":"en-US"', '[a-z]', upper(substr('"language":"en-US"', 2, 1)), 1, 1, 'i') 
from dual ; 


SQL> select regexp_replace('"language":"en-US"', '[a-z]', upper(substr('"language":"en-US"', 2, 1)), 1, 1, 'i') language from dual

LANGUAGE
------------------
"Language":"en-US"

SQL>

This implementation assumes that the first letter is the one you want to convert but this letter is after an expected double quote character. (second place in the string)

Regards

Daniel Vukasovich
  • 1,692
  • 1
  • 18
  • 26